Subscribe Bookmark RSS Feed

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

apploarcher

New Contributor

Joined:

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"

Output:

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!

2 REPLIES
txnelson

Super User

Joined:

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

 

Jim
apploarcher

New Contributor

Joined:

Jan 22, 2017

Sorry for the late reply! Thanks for the solution!