topic Re: Ranking function not ranking properly when there are duplicate values. How to fix this? in QlikView

Ranking function not ranking properly when there are duplicate values. How to fix this?

jblomqvist — Thu, 01 Oct 2015 15:10:33 GMT

Hi all

I have the following output in my Straight table:

You can see the rank function in the last column does x - y when there are duplicate values.

How can I get the ranking to not do that and instead assign the same number for the duplicate values?

Maybe the rank function is not the right thing to use and I am open to other suggestions.

As an example, what I would like is 9-10 output in the two rows above to be 9 for both and then assign 10 for the next person.

Any ideas how to do this?

My chart above has

EmployeeName as Dimension

Expression is Sum(Salary)

Ranking expression is Rank(Sum(Salary))

Any help would be great

Re: Ranking function not ranking properly when there are duplicate values. How to fix this?

JonnyPoole — Thu, 01 Oct 2015 15:13:10 GMT

There is a parameter in the rank() function for that.

by default the 2nd parameter is 0 for 'mode' but sounds like you want 1 or 3 ?

Re: Ranking function not ranking properly when there are duplicate values. How to fix this?

marcus_sommer — Thu, 01 Oct 2015 15:15:30 GMT

If you need this in a sorted table you could simply use rowno(total).

- Marcus

Re: Ranking function not ranking properly when there are duplicate values. How to fix this?

MK_QSL — Thu, 01 Oct 2015 15:20:19 GMT

=Num(Rank(Total Sum([Year Salary]),4))

Re: Ranking function not ranking properly when there are duplicate values. How to fix this?

jblomqvist — Thu, 01 Oct 2015 15:21:43 GMT

Hi Jonathan,

I tried that but it made no impact to my results. They stay the same.

Re: Ranking function not ranking properly when there are duplicate values. How to fix this?

JonnyPoole — Thu, 01 Oct 2015 15:29:55 GMT

Sorry - looks like the 3rd parameter is also needed to get rid of the x-y values . But i do like Manish's approach too

IE: rank( <expression> ,1,1)

Re: Ranking function not ranking properly when there are duplicate values. How to fix this?

jblomqvist — Thu, 01 Oct 2015 15:59:04 GMT

Hi Manish,

This seems to do the job. Could you please explain what the 3 and num function does to make it work?

I tried the 3 without the num and it did not work.

Re: Ranking function not ranking properly when there are duplicate values. How to fix this?

MK_QSL — Thu, 01 Oct 2015 16:17:07 GMT

Rank (expression,4)

Will give incremental rank... i.e. if there are lines having same Rank, it will allocated incremental rank... Say there lines are having rank 4-6, this expression will allocate rank 4 5 and 6 to different lines... You can read reference manual for further information and to understand the difference between 2nd and 3rd parameters

Re: Ranking function not ranking properly when there are duplicate values. How to fix this?

Thu, 31 Dec 2015 09:24:20 GMT

Just set expression value in Number as integer.you will get the proper ranking