Hi, I have a problem where I want to formulate a new column as a benchmark to all other countries. If there are more rows for the country, I want to take the max value of it as the benchmark. See the example below for clarification.
Data kind of looks like this:
Country Value period
A 10 201301
A 30 201301
B 20 201301
C 50 201301
D 40 201301
What I want to achieve (with cell formula): Col Maximum (:value, :period, :country) where :country == "A"
Country Value period BenchMark
A 10 201301 30
A 30 201301 30
B 20 201301 30
C 50 201301 30
D 40 201301 30
As you can see, I want to benchmark the max value of only country A.
I tried Col Maximum (:value, :period, :country == "A") and it will return "50"
Col Maximum (:value, :period, :country) and Col Maximum (:value, :period, :country == "A") both returns "50"
I am pretty sure there's a simple solution for this!
Hope you guys can help!
Given the way you stated your needs, Assuming you know what the country that has the multiple entries.
dt = Current Data Table(); rowperiod = :Period[Row()]; Max( :Value[dt << get rows where( :Country == "A" & :period == rowperiod )] );