Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

Simple Rank() question

Hi,

Say i have the following table:

ContributorPoints
James

300

Lisa290
Jake290
Kelly150
Sara123
Fidel110

In my eyes ranking the table should gives the following rank

ContributorPoints
James

1

Lisa2
Jake2
Kelly3
Sara4
Fidel5

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

Tags (1)
1 Solution

Accepted Solutions
MVP
MVP

Re: Simple Rank() question

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

104546.jpg

9 Replies
Employee
Employee

Re: Simple Rank() question

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

Not applicable

Re: Simple Rank() question

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

Thanks to HIC for this.

Re: Simple Rank() question

Rank(SUM(Points),0,1)

Not applicable

Re: Simple Rank() question

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

Hi

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.
1Lowest rank on all rows.
2Average rank on all rows.
3Highest rank on all rows.
4Lowest 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).
1Low value on all rows.
2Low value on first row, blank on the following rows.
MVP
MVP

Re: Simple Rank() question

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

104546.jpg

nizamsha
Valued Contributor

Re: Simple Rank() question

num(rank(Points1,4))

Not applicable

Re: Simple Rank() question

Bingo! Thanks!!!

christian77
Valued Contributor

Re: Simple Rank() question

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.

Community Browser