topic Re: Simple Rank() question in QlikView

Simple Rank() question

Fri, 17 Jan 2014 12:43:36 GMT

Hi,

Say i have the following table:

Contributor	Points
James	300
Lisa	290
Jake	290
Kelly	150
Sara	123
Fidel	110

In my eyes ranking the table should gives the following rank

Contributor	Points
James	1
Lisa	2
Jake	2
Kelly	3
Sara	4
Fidel	5

Using Rank() function, Qlikview will set Lisa and Jake both on position '2-3'.

Is it possible to give Jake and Lisa the well deserved second place ?(after all they work really hard for it

Dror

Re: Simple Rank() question

hic — Fri, 17 Jan 2014 13:17:46 GMT

The Rank() function is Dual and all you need to do is to use Num(Rank(...)) to get what you want. You can also play around with the second parameter - mode - of the Rank function.

HIC

Re: Simple Rank() question

Fri, 17 Jan 2014 13:21:22 GMT

you can use : num(rank(Points,1)) in your case.

Thanks to HIC for this.

Re: Simple Rank() question

MK_QSL — Fri, 17 Jan 2014 13:25:46 GMT

Rank(SUM(Points),0,1)

Re: Simple Rank() question

Fri, 17 Jan 2014 13:27:21 GMT

Hi,

Try use this:

num( rank( sum( Points ), 1))

In this mode will get the lowest rank for Lisa and Jake, in this case, 2.

Re: Simple Rank() question

MayilVahanan — Fri, 17 Jan 2014 13:49:18 GMT

Try like this

rank( sum( Points ), 1)

From help:

rank([ total ] expression [ , mode [, format ] ])

The second parameter mode specifies the number representation of the function result.

mode

0 (default)	If all ranks within the sharing group fall on the low side of the middle value of the entire ranking, all rows get the lowest rank within the sharing group. If all ranks within the sharing group fall on the high side of the middle value of the entire ranking, all rows get the highest rank within the sharing group. If ranks within the sharing group span over the middle value of the entire ranking, all rows get the value corresponding to the average of the top and bottom ranking in the entire column segment.
1	Lowest rank on all rows.
2	Average rank on all rows.
3	Highest rank on all rows.
4	Lowest rank on first row, then incremented by one for each row.

The third parameter format specifies the text representation of the function result.

format

0 (default)	Low value&' - '&high value on all rows (e.g. 3 - 4).
1	Low value on all rows.
2	Low value on first row, blank on the following rows.

Re: Simple Rank() question

maxgro — Fri, 17 Jan 2014 14:12:29 GMT

I tried to get consecutive ranking and I came to the following expression

Div(

Index(

'-' & Concat(DISTINCT TOTAL num(aggr(rank(Points,1,1),Name), '0000'), '-', -Points),

num(rank(Points,1,1),'0000' )

)

,5)+1

Re: Simple Rank() question

nizamsha — Fri, 17 Jan 2014 14:28:34 GMT

num(rank(Points1,4))

Re: Simple Rank() question

Wed, 22 Jan 2014 22:01:44 GMT

Bingo! Thanks!!!

Re: Simple Rank() question

christian77 — Fri, 11 Apr 2014 07:48:05 GMT

Hi. I've just placed a topic about this funcktion.

The second parameter does not work.

The NAME Rank does it backwards.

-----------This text editor does not allow me to copy anything here. The topic was placed a while ago.

RANK FUNCTION DOES NOT WORK.

Do I need help or it is a bug?

Thanks.