How to calculate relativ values based on a position of a base value?

[#1]

Hello everybody,

I need to calculate percentage share of values of the column Value1 based on the position of the selected value from column Value1. As an example if I want to do that based on the second value in column Value1 (position 2, or value 2), the resulting column Value1_rel with relative values should look like this:

Additionally, there can be more data which needs to be grouped by Item and then for each item calculated percentage share (maybe  normalized or relative value is a bit confusing term) for all other values. How can I do that? If the explanation is not good enough, please forgive and feel free to ask.

Thank you and best regards

[#2]

Value1_rel could be a formula column.

I'm not sure what the formula would be based on your example data.

Usually I'd expect relative to mean Value1 - Param1, or Value1 - 0, but neither matches the example.

Edit: I think the formula for Value1_rel that matches your data is 2 / Value1. You can put that in the column formula. But...

I suspect you have zero, from somewhere, and want to look up the row number 2 from zero. JMP data tables are usually arranged with all of the data on one row belonging together. It might make sense to put the Param values as a column property of the Value column. That will take a bit more JSL scripting, but might reflect the organization of your data better.

[#3]

Thank you for comment. Yes, there was zero in the table in the previous version of post but I thought that might be a bit confusing so I thought that maybe it will be simpler to ask about calculating the desired values taking the value from the second row of each item group. And yes, the "zero" from the Param1 column was to only determine the row number.

[#4 · Accepted solution]

Hi,

You can use this admittedly complex formula (perhaps someone will present an easier one) in a new column:

Eval(
	Eval Expr(
		:Val[(:Group << get data table) << get rows where(
			Col Cumulative Sum( 1, :Group ) == 2 & :Group == Expr( :Group[Row()] )
		)] / :Val
	)
)

It is probably worth unpacking this at least a little.

The Eval( Eval Expr( )) construct is used to force evaluation of the expression Expr ( :Group[Row()] ). Without this, several values will be returned on each row... one for each group. Try it by removing the Eval ( Eval Expr ()) wrapper and the Expr() wrapper to see what I mean.

To determine the value we'll use in the ratio, we need to grab the 2nd row within the current group. To find this row, we must match on group (the second part of the get rows where clause) and must also match as the 2nd value within the group. An easy way to do this is to look at the cumulative sum of a column of 1s, grouped by :Group. When this column == 2, we have the 2nd value of some group. This happens in several rows, but the 2nd part of the clause restricts the result to the group matching the group of the current row. In this way, the table doesn't even need to be sorted by group or value.

Changing the 2 in the cumulative sum equivalence to k, allows you to use the kth value in the group instead of the 2nd.

Cheers,

Brady

[#5]

Hi Brady, thank you very much for code and explanation. It works perfectly. Best regards

[#6]

Hi Brady,

hopefully this is allowed even though the original question has already been answered.

I have a similar problem as lukasz, and I've been trying to tweak your (very good) answer to fit my problem.
Basically I am also trying to divide one column by itself, however I am not using the 2nd (or kth) as reference but instead I have a third column named label which I want to use to find the reference points. The problem I have is that I can't figure out how to get the "by-loop" working in my case, all my script does now is it divides every value by the first value named "big" (I understand why, but not how to fix it).

c << new column("Rel", numeric, continuous, formula(
	Eval(
	Eval Expr(
		:Val[(:Group << get data table) << get rows where(
			label == "big" & :Group == Expr( :Group[Row()] )
		)] / :Val
	)
));
)

/ V