topic Re: Rank identical values in QlikView

Rank identical values

Tue, 13 Nov 2012 14:32:26 GMT

Hi All,

Apologies for posting this, but I have gone through every discussion with the word RANK in it and still not find the answer but I am sure there must be one to this question.

I have a pivot table with to dimensions: "Company" and "Salesman" and one expression which is as simple as they come: Count(Sales_ID) which gives me a count of the number of unique sales per Company and Salesman.

I then have wanted to rank them, so I would end up with something like:

Company Salesman Count of Sales Rank

ABC GH 23 1

ABC TR 21 2

ABC JP 21 3

DEF BN 54 1

DEF VC 32 2

DEF OK 32 3

However my problem is that the ranking function, regardless of how I use it (in various combinations with aggr function, etc) doesn't work like I would like it to work - ie: identical count values are ranked the same, so 21 has a rank of 2 for both salesmen for company ABC, and so on.

In SQL I would use a combination of Rank and Partition by to solve this issue, usually using something like a rowno etc to act as the decider when identical count values are present, but none of my attempts in Qlikview seem to have worked.

Could someone please tell me how I can break up my rankings so they are sequential and don't provide the same rank for identical values please?

Thanks,

Ross

Re: Rank identical values

Gysbert_Wassenaar — Tue, 13 Nov 2012 14:46:59 GMT

Try: num(rank(CountOfSales,4))

Re: Rank identical values

Tue, 13 Nov 2012 14:50:50 GMT

Thank you. Apologies for such a silly question but it was incredibly frustrating to not be able to find this answer. I usually do much of my work in SQL so Qlikview is relatively new to me.

Re: Rank identical values

Tue, 28 May 2013 23:14:42 GMT

Hello Gysbert, I ran into this same problem and your solution worked. That being said, I'm not sure why, based on my reading of what the num function does. Can you please explain why this works for removing duplicates?

Re: Rank identical values

Tue, 28 May 2013 23:14:48 GMT

Re: Rank identical values

Tue, 28 May 2013 23:14:56 GMT

Re: Rank identical values

Marcos_Ferreira_dos_Santos — Fri, 13 Apr 2018 19:59:23 GMT

Hi folks!

Gysbert,

Thank you very much! Your answer helped me a lot this week.

Lewis (and anyone with the same question):

I read the following post linked to another Thread about this issue:

https://help.qlik.com/en-US/qlikview/12.1/Subsystems/Client/Content/Examples%20of%20Chart%20Ranking%20functions.htm?_ga=…

It seems that the use of NUM function in this case is a workaround for this necessity...

Qlik personnel,

Please, I would suggest you to include this information on the help/Manual Reference to the RANK function. It will save time for many developers in the future.

Best regards to all,

Marcos

Re: Rank identical values

dberkesacn — Fri, 10 Aug 2018 13:48:33 GMT

Hi,

Some months have passed but, I used this to create a top 3 bottom 3 dimension, it is also handle if we have identical values (or calc. output).

=aggr(

if(

num(rank(aggr(sum(value),id),4)) >= max(total aggr(rowno(total),id),3) or

num(rank(aggr(sum(value),id),4)) <= min(total aggr(rowno(total),id),3)

,id

),id)

Dan

Re: Rank identical values

Flosom — Sun, 29 Aug 2021 06:58:44 GMT

Wow!! superb solution