BookmarkSubscribeRSS Feed

How do I use the Col Maximum Formula with a "where" condition


Community Trekker


Jan 22, 2017

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!


Thanks thanks!


Super User


Jun 22, 2012

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()];
	:Value[dt << get rows where(
		:Country == "A" & :period == rowperiod



Community Trekker


Jan 22, 2017

Sorry for the late reply! Thanks for the solution!