Rank identical values

[#1]

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

[Gysbert_Wassenaar · Accepted solution]

Try: num(rank(CountOfSales,4))

[#3]

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.

[#4]

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? 

[#5]

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? 

[#6]

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? 

[Marcos_Ferreira_dos_Santos]

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%...

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

[dberkesacn]

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

[Flosom]

Wow!! superb solution