topic rank fun? in QlikView

rank fun?

Fri, 01 Aug 2014 07:31:35 GMT

rank(sum(sales),0)

rank(sum(sales),1)

rank(sum(sales),2)

rank(sum(sales),3)

all are giving same result what is meant by writing '0' here and remaining 1,2,3, what it do?

Thanks in advance

Re: rank fun?

Fri, 01 Aug 2014 07:44:21 GMT

Hi,

Rank function has three argument

1st argument is expression, 2nd argument is mode and 3rd argument is format

for mode you can have following value

by default value is 0

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.

You can also find this info in qlikview help.

Regards,

Vivek

Re: rank fun?

Fri, 01 Aug 2014 07:48:55 GMT

for example

rank(sum(sales) output is like

sales:

if i go with rank(sum(sales),0) my question is here rank(sum(sales) value is comaparing with '0' ?

Re: rank fun?

Fri, 01 Aug 2014 08:02:42 GMT

Hi,

it is not comparing with zero

What happens suppose you have same value for different rows, Like

Field,Value

A, 12

B, 15

C, 15

Then in this case Value for B and C is same, then How you want to represent this,

if you will give 1 then rank for B and C will be 2 (Lowest rank Value)

if you will give 2 then rank for B and C will be 2.5 (Avg of ranks)

if you will give 3 then rank for B and C will be 3 (Highest of ranks)

if you will give 4 then rank for B and C will be 2 and 3

Hope this helps !

Vivek

Re: rank fun?

Fri, 01 Aug 2014 08:10:02 GMT

Excellent awesome thank you vivek

Re: rank fun?

Fri, 01 Aug 2014 09:32:02 GMT

Hi Vivek,

Can you share a sample qvw with this functionality of Rank?

Appreciate your help.

Thanks

Sabal

Re: rank fun?

Fri, 01 Aug 2014 10:15:13 GMT

Hi,

Please find attached document

Regards,

Vivek

Re: rank fun?

Fri, 01 Aug 2014 10:17:45 GMT

Thanks a lot Vivek