Re: How to calculate relativ values based on a position of a base value?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.
How to calculate relativ values based on a position of a base value?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
Data TableScriptingRe: How to calculate relativ values based on a position of a base value?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.Re: How to calculate relativ values based on a position of a base value?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
