Creating a delta column

[#1]

Hi folks,

I am trying to create a delta column.

the difference is between mean(normalised) - mean(normalised of the "MATCHED" product)

I tried the lag function but this doesnt work as no date.

Would I have to create a new column "Matched_Value" and set all row values to the Same as the MATCHED value

then create my delta = Mean(no...) - Matched...

[#2 · Accepted solution]

This formula is an easy way to do what you want

If( Row() == 1,
	matched = :"Mean(Normalised)"n[(Current Data Table() << get rows where( :PRODUCT == "MATCHED" ))[1]]
);
:"Mean(Normalised)"n - matched;

[#3 · Accepted solution]

You won't have to create such a column, but it is quite easy way to solve this.

You could create formula which would use for example Loc() to get the index of MATCHED PRODUCT and then use that to get value from Mean(Normalized) column. For example something like this:

:Name("Mean(Normalised)") - :Name("Mean(Normalised)")[Loc(:PRODUCT << get values, "MATCHED")[1]]

I suggest you take a look in the formula menu and see what subexpressions return or break the example into multiple formulas to understand properly what it does (and for example why there is [1] after Loc).

[#4]

Thank you guys for both formulas