Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All
i have created a table in Qlik that, when fully displayed, looks something like the below.
CAUSEDESCRIPTION | CLAIMNO |
Insured 1 | 177 |
Insured 2 | 145 |
Insured 3 | 81 |
Insured 4 | 78 |
Insured 5 | 45 |
Insured 6 | 45 |
Insured 7 | 27 |
Insured 8 | 19 |
Insured 9 | 18 |
Insured 10 | 13 |
Insured 11 | 8 |
Insured 12 | 5 |
Insured 13 | 3 |
TOTAL | 664 |
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
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
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
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).
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
worked perfectly. thank you