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

[jblomqvist]

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

[JonnyPoole]

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 ?

[marcus_sommer]

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

- Marcus

[MK_QSL]

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

[jblomqvist]

Hi Jonathan,

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

[JonnyPoole]

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)

[jblomqvist]

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.

[MK_QSL]

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

[#9]

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