Get the Dimension Value of an Aggregation Result

[caio_caminoski]

Hi guys!

I have a question! I have the following situation:

I want to create a text box that gives me the year with more 'counts'. In this case it would be '2003', because it is the year with '7 counts'.

Which expression should I use in my text box?

Thanks in advance!

[swuehl · Accepted solution]

Try FirstSortedValue() function:

=FirstSortedValue( Year, aggr(-count(Name), Year))

[caio_caminoski]

That worked! Thanks!

However if I select from 1996 to 2002 I would have two years with the same number (2001 and 2002 would be '4'). How can I get the max year between the two of them?

I really appreciate your help!!

Best regards

Caio

[swuehl]

This should work:

=FirstSortedValue( Year, aggr(-(count(Name)+Year/10000), Year))

[caio_caminoski]

Works beautifuly, thank you again!!

But I still trying to understand what that '/10000' is doing there!

[swuehl]

It divides the Year value by 10000, adding a small number on top of the count(Name) to break potential ties.