How do I edit a RANK statement ?

[#1]

Hi Experts,

I want to rank a field based on its value. The RANK statement is ideal for this purpose. However, if I have values in the column which are the same then QlikView displays the range of ordinal numbers.

For example, if the top 4 records all have the same value (67) QlikView displays the rank of each as 1-4. However, I want the rankings to display as 1, because they are all in equal first position.

How do I do this ?

Thanks

MV

[swuehl]

There are two more optional arguments to rank() function you can use for that (refer to the HELP):

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.

So, this should return what you want:

=rank(YourExpression,1,1)