First Sorted Valued, Max & Set Analysis

[dvugopietro]

Hi, I'm new to Qlik.  I want to resolve the following problem in the Presentation Layer using SetAnalysis and Expression (I can't use scripting).

ID     COUNT

AA    3

BB     1

CC     4

AA     1

I want to extract the ID with the MAX(SUM(COUNT) grouped by ID) value.

In the above example I want to show both AA and CC values (4).

I thinked the following expression: =FirstSortedValue(ID,-Aggr(Sum([COUNT]),ID),1) but when there are more than one ID with the same MAX(SUM(COUNT) grouped by ID value, FirstSortedValue return NULL.

How can I write an expression with the same above purpose ?

I thinked to use Set Analysis combined with Concatenate and Max functions, but I can't figured how approaches the solution.

Thanks you.

[sunny_talwar · Accepted solution]

May be use this instead

Concat(Aggr(If(Sum(COUNT) = Max(TOTAL Aggr(Sum(COUNT), ID)), ID), ID), ', ')

[dvugopietro]

Hello Sunny.

Thanks you. The result is correct.