Qlik Community

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
WE ARE LISTENING! New Navigation for Qlik Community, Sept. 26: TELL ME MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
david_pearson
Contributor III
Contributor III

% of total on top 10 table

Hi All

i have created a table in Qlik that, when fully displayed, looks something like the below.

CAUSEDESCRIPTIONCLAIMNO
Insured 1177
Insured 2145
Insured 381
Insured 478
Insured 545
Insured 645
Insured 727
Insured 819
Insured 918
Insured 1013
Insured 118
Insured 125
Insured 133
TOTAL664

 

I wanted to show the top 10 causes and include the count of CLAIMNO, i set to top 10 in the dimension. it displayed the correct number of fields and with the correct count of CLAIMNO assigned to each of the top 10 CAUSEDESCRIPTION filed with the exception of the column total. this still showed 664 even through if you were to add up when was displayed it would sum to 648.

To get around this i found i need to do the following dimension 

IF(Aggr(Rank(SUM(CLAIMNO),4),CAUSEDESCRIPTION)<=10,CAUSEDESCRIPTION)

 

this now shows everything as i expect. Great! i then wanted to know what each of the top 10 CAUSEDESCRIPTION % was in comparison to the total of the top 10. i.e. Insured 1 would calc as 177/648. i had expected to merely do count(CLAIMNO)/Count(Total CLAIMNO) but this is calculating with 177/664.

so my actual question is how do i do the calculation so it uses the 648 as the division rather than the full table total?

thanks

1 Solution

Accepted Solutions
kashyap311996101
Partner - Specialist
Partner - Specialist

Hi David,

For Top10: You can use this Expression

                     Count({<ProductName = {"=Rank(Count(OrderID))<=10"} >}OrderID)

For Total of Top10 :

                    Count(Total {<ProductName = {"=Rank(Count(OrderID))<=10"} >}OrderID)

For percentage Calculation:

                   Count({<ProductName = {"=Rank(Count(OrderID))<=10"} >}OrderID)
                   / Count(Total {<ProductName = {"=Rank(Sum(Quantity))<=10"} >}OrderID)

 

output is attached

Hope it works for you

Thanks and Regards
Kashyap.R

View solution in original post

5 Replies
Gui_Approbato
Creator III
Creator III

Not sure if I got that right, but try this:

 

Instead of that expression as dimension, use the if inside the aggr():

Aggr( if(Rank(Sum( Total CLAIMNO))<=10, CAUSEDESCRIPTION), CAUSEDESCRIPTION)

and then, as measuse, try the Count(CLAIMNO) as usual, otherwise try

 

Aggr( if(Rank(Sum( Total CLAIMNO))<=10, Count(CLAIMNO)), CAUSEDESCRIPTION)

Hope it helps

david_pearson
Contributor III
Contributor III
Author

Aviva: Public

Thank but although Qlik does accept the dimension as correct it unfortunately this clears all fields to blanks or nulls when I put your dimension in place and count(CLAIMNO)
[cid:image001.png@01D56169.5A25E170]
david_pearson
Contributor III
Contributor III
Author

Thank but although Qlik does accept the dimension as correct it unfortunately then clears all fields to blanks or nulls when I put your dimension in place and try either count(CLAIMNO) or Aggr( if(Rank(Sum( Total CLAIMNO))<=10, Count(CLAIMNO)), CAUSEDESCRIPTION).

clipboard_image_0.png

kashyap311996101
Partner - Specialist
Partner - Specialist

Hi David,

For Top10: You can use this Expression

                     Count({<ProductName = {"=Rank(Count(OrderID))<=10"} >}OrderID)

For Total of Top10 :

                    Count(Total {<ProductName = {"=Rank(Count(OrderID))<=10"} >}OrderID)

For percentage Calculation:

                   Count({<ProductName = {"=Rank(Count(OrderID))<=10"} >}OrderID)
                   / Count(Total {<ProductName = {"=Rank(Sum(Quantity))<=10"} >}OrderID)

 

output is attached

Hope it works for you

Thanks and Regards
Kashyap.R
david_pearson
Contributor III
Contributor III
Author

worked perfectly. thank you