Rank Function

[Anonymous]

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

[tresesco]

Try like:

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

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

[Anonymous]

It is repeating same rank multiple times....

Say:

Cust Id  Values     Rank

1             0              1

2             0               1

3              0               1

[tresesco]

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.

[#5]

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

[Anonymous]

Anand,

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

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

[#7]

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

[#8]

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

[Anonymous]

Thanks Prince.

Very much helpful.

[#10]

Welcome Balraj

Thanks& Regards

Prince Anand