3 Replies Latest reply: Jan 28, 2016 7:00 AM by Disha Ahuja

Extracting data in Qlik sense for different maximum values

Hi All,

I have a following kind of data.

NameIdDate1Date2Count
A0@@@@10
A1@@@@11
B0@@@@12
B1@@@@12
B2@@@@10
B3@@@@9
C0@@@@15
C1@@@@15
C2@@@@15
D0@@@@18
D1@@@@21
E0@@@@20
E1@@@@10
E2@@@@18
E3@@@@13
E4@@@@13

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

The result should be something like the following.

NameIdDate1Date2Count
A1@@@@11
B3@@@@9
C2@@@@15
D1@@@@21
E4@@@@13

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

The result should be:

D: 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?

Regards,

Disha Ahuja

• Re: Extracting data in Qlik sense for different maximum values

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.

• Re: Extracting data in Qlik sense for different maximum values

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.

• Re: Extracting data in Qlik sense for different maximum values

Thank you for your help.