Lookup Name of a Max Aggr Count

[#1]

Hello, I'm new to the Forums. I'm trying to find out if there is a way / how to get the associated value from a Max(Aggr(Count())) expression.

Specifically, I have a data set with 4 regions. Each region has a different total number of territories.

To begin, I'm using the formula max(aggr(count([Territory]),[Region])) to get the value of the region with the most territories.

Example:

Region A = 250 territories

Region B = 50 territories

Region C = 300 territories

Region D = 100 territories

So my formula returns 300.

Now I'm trying to put this in a text box to say 'The Region with the most territories is [Region Name] with [Count of Territories] Territories.

ex. "The Region with the Most Territories is Region C, with 300 Territories."

What formula can i use to populate the [Region Name] in my above example?

It's late, so hopefully there is an easy way and I'm just overthinking, but any help is much appreciated.

[sunny_talwar · Accepted solution]

To get that, you can use FirstSortedValue() function:

=FirstSortedValue(Region, -Aggr(Count(Territory), Region))

[#3]

Well ain't that some stuff. You nailed it Sunny... thank you so much for your help! That's exactly what I was trying to do. +10 master class points.

[effinty2112]

Hi Travis,

I know this question has been answered well by Sunny but I would like to offer an alternative to FirstSortedValue, an alternative which is a little bit harder to follow but has an advantage which, I think, makes it worthwhile knowing.

I have a little problem with the FirstSortedValue function as it fails if there is tie for first place (or what ever place in the ranking you are looking for should you enter the appropriate perimeter).

If your data set was this instead:

Region	Territories
A	250
B	50
C	300
D	300

The FirstSortedValue would return a null value. An alternative is to use the function

=Concat(Aggr(if(Rank(Sum(Territories),1,1)=1, Region),Region),',')

which returns C,D in this case.

I think this behaviour if there is a tie is a bit of a flaw in the FirstSortedValue function and it puts me off using it.

Cheers

Andrew

[sunny_talwar]

Thanks for bringing this up Andrew. I guess there are variety of things that can happen which may need the solution to be altered. But I usually like FirstSortedValue because it is very straight forward for most of the instances except few where it chokes.

In addition to your proposed solution, I think the same can be done using Set analysis rather than the if statement

=Concat({<Region = {"=Rank(Sum(Territories),1,1)=1"}>}Region, ', ')

or

=Concat({<Region = {"=Sum(Territories)=Max(TOTAL Aggr(Sum(Territories), Region))"}>}Region, ', ')

I hope you will continue to help me become more efficient

[sunny_talwar]

I am glad I was able to help Travis

[effinty2112]

Hi Sunny,

Your idea to use set analysis rather than aggr to get around the problem of a tie is one I hadn't thought of and I expect it would be more efficient for large data sets.

Thanks!

Andrew