topic Re: Rank Function in QlikView

Rank Function

Anonymous — Mon, 13 Oct 2014 07:23:39 GMT

Dear Experts,

I am using a Rank Function. In my case if same values is repeating multiple time then it is assigning a rank like 200-250.

And I want it should be like this:

Values Rank

0 200 not 200-250

0 201

0 202

0 203

Please suggest, how it is possible?

Regards,

Balraj

Re: Rank Function

tresB — Mon, 13 Oct 2014 07:36:45 GMT

Try like:

Rank(Sum(Amount),1,1)+RowNo()-1

You might have to play a bit with this rowno() in your actual scenario.

Re: Rank Function

Anonymous — Mon, 13 Oct 2014 07:46:09 GMT

It is repeating same rank multiple times....

Say:

Cust Id Values Rank

1 0 1

2 0 1

3 0 1

Re: Rank Function

tresB — Mon, 13 Oct 2014 07:58:51 GMT

You have multiple dimension. Hence try: =Rank(Sum(Amount),1,1)+RowNo(Total)-1

Which is eventually something equivalent to RowNo(Total), provided the sort order is correct. If your chart is not sorted, this could not be useful.

Re: Rank Function

Mon, 13 Oct 2014 09:07:00 GMT

Dear Balraj,

Use the following Rant Function for the required Solution:-

=num(rank(total sum(Values),4,1))

and also find the attached QVW.

Thanks & Regards

Prince Anand

Re: Rank Function

Anonymous — Mon, 13 Oct 2014 10:00:59 GMT

Anand,

Could you explain rank(total sum(Values),4,1), what it will do??

I mean to say the parameters (4,1)....

Re: Re: Rank Function

Mon, 13 Oct 2014 10:13:40 GMT

The Rank Function has Following Format:-

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

mode has 5 Values:- 1,2,3,4

0 (Default) for :-

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 for:- Lowest rank on all rows.

2 For:- Average rank on all rows.

3 For:- Highest rank on all rows.

4 For:- Lowest rank on first row, then incremented by one for each row.

So I use Mode as 4.

.and the Parameter Format Can have 3 Values:- 0(Default),1,2

0 For:-Low value&' - '&high value on all rows (e.g. 3 - 4).

1 For:- Low value on all rows.

2 For:- Low value on first row, blank on the following rows.

So Yoy can also use the formula as:-

=num(rank(total sum(Values),4))

Thanks & Regards

Prince Anand

Re: Rank Function

Mon, 13 Oct 2014 10:18:24 GMT

So, I use Mode as 4 because Mode 4 returns Lowest rank on first row, then incremented by one for each row.

Thanks & Regards

Prince Anand

Re: Rank Function

Anonymous — Mon, 13 Oct 2014 11:19:57 GMT

Thanks Prince.

Very much helpful.

Re: Rank Function

Mon, 13 Oct 2014 11:28:34 GMT

Welcome Balraj

Thanks& Regards

Prince Anand