Understanding Rank in KPI

[samnoor_27]

Hi,

I wish to understand how the Rank function actually works.

In the table "Ranking" in attachment, I wish to get the top 5 Organization and displays the count of the top 5 in a KPI object.

My set analysis to get the top 5 and compute the total number of Tickets is as follows:

=Count({<Organization = {'=Rank(Count(Ticket))<= 5' }>}Ticket)

Logically I should be getting 10 as result (3+2+2+2+1), but I fail to understand why the set analysis returns 9!

FYI: top 2 to top 13 results in 9

As from top 14, result is 31

Thanks to advise on this issue

[JordyWegman]

Hi,

The problem lies in using Organization in your set analysis. Replace this by Ticket and it will work:

=Count({< Ticket = {'=Rank(Count(Ticket))<= 5' }>}Ticket)

Jordy

Climber 

[samnoor_27]

Hi @JordyWegman ,

Thanks for your reply. I'm afraid this time it returns me total count of the 26 Organizations, which is 31.

Kind Regards,

[JordyWegman]

Then we'll try something else. Is the count of organizations equal to the count of tickets? Try to add Count(Organization) to your table and see if they match with Count(Ticket).

If it's the same, then you could use this:

=Count({< Organization = {'=Rank(Count(Organization))<= 5' }>}Organization)

Jordy

Climber 

[tresesco]

Could you share your sample app that demonstrates the issue? 

[samnoor_27]

Hi @tresesco ,

Please find attached the qvf as well as excel file.

Thanks for your help!

[samnoor_27]

Hi @JordyWegman ,

Yeah we can say Count(Organizations) equals to Count(Tickets), but unfortunately still not getting the desired result :(..

Don't you think it has something to do with how Qlik uses the function Rank?

Kind Regards,

[tresesco]

When you use rank<=5, then it considers till 4th row of your table. And that is because when you see a rank 5-26 it's a dual value which has a number at the backend as 13.5 (use num() around your rank(), you would see that).

Hope this clarifies

[samnoor_27]

Hi @tresesco ,

Thanks for your explanation.

Ok...it kind of makes sense..

But does it mean, I can't compute and display the count for top 5 Organization to return 10? Or is there an alternative for that?

Kind Regards,

[sunny_talwar · Accepted solution]

You can try to use this

=Count({<Organization = {'=Rank(Count(Ticket), 4) <= 5' }>}Ticket)