First Sorted Valued, Max & Set Analysis

dvugopietro — Wed, 25 Nov 2020 16:16:04 GMT

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.

Re: First Sorted Valued, Max & Set Analysis

sunny_talwar — Fri, 22 Jun 2018 16:12:56 GMT

May be use this instead

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

Re: First Sorted Valued, Max & Set Analysis

dvugopietro — Fri, 22 Jun 2018 17:43:24 GMT

Hello Sunny.

Thanks you. The result is correct.

topic First Sorted Valued, Max & Set Analysis in QlikView

First Sorted Valued, Max & Set Analysis

Re: First Sorted Valued, Max & Set Analysis

Re: First Sorted Valued, Max & Set Analysis