Extracting data in Qlik sense for different maximum values

[#1]

Hi All,

I have a following kind of data.

Name	Id	Date1	Date2	Count
A	0	@@	@@	10
A	1	@@	@@	11
B	0	@@	@@	12
B	1	@@	@@	12
B	2	@@	@@	10
B	3	@@	@@	9
C	0	@@	@@	15
C	1	@@	@@	15
C	2	@@	@@	15
D	0	@@	@@	18
D	1	@@	@@	21
E	0	@@	@@	20
E	1	@@	@@	10
E	2	@@	@@	18
E	3	@@	@@	13
E	4	@@	@@	13

Now, I first want count for each Name corresponding to its respective maximum id.

The result should be something like the following.

Name	Id	Date1	Date2	Count
A	1	@@	@@	11
B	3	@@	@@	9
C	2	@@	@@	15
D	1	@@	@@	21
E	4	@@	@@	13

Now out of this intermediate result, I want to find top 3 name on the basis of count.

The result should be:

😧 21

C: 15

E: 13

I have to show this count and name in the KPI visualization.

Is there a way this can be done in Qliksense?

May be we can some how extract the intermediate table in Load Script via some expressions and then the final result can be easily calculated in visualization by means of functions (firstsortedvalue(), aggr(). This part i am aware of!! )

Or some other way, we can achieve this?

Please help. In a crisis currently.

Regards,

Disha Ahuja

[#2]

I know AGGR(MAX(Count),Name) will give you the MAX for each Name and Set Analysis will likely be able to use this to produce a count of the MAX, but unfortunately the syntax currently escapes me!

Will keep trying!

Once you have the list, you can set a "Limitation" to show the top 3.

[reddy-s]

Hi Disha,

So as Andrew mentioned the Aggr function should help you achieve the intermediate result and the properties panel can be used to restrict the no of rows. How ever, doing this in the load script will be a better practice.

[#4]

Thank you for your help.