Calculate with split ranks

[hugmarcel]

Hi

I have a pivot table with a column named "ranks" indicating ranks. Some ranks are split ranks (22-23, 3-4, etc.).

If I add another formula x to the pivot, where x = [ranks] + 1, it turns out that the split ranks are not calculated correctly.

Instead of 23.5 (for 22-23) I get 23, and instead of 4.5 I get 4.

What formula do I have to use to get the calculation correct?

Thanks - Marcel

[tresesco]

Rank could vary with change in number of rows in your chart. May be the additional column is causing some additional rows. Could you are a representative sample qvw?

[hugmarcel]

Hi

no, the additional column is not causing additional rows. I try to create a qwv.

thx - Marcel

[hugmarcel]

I have now attached the sample. see there

[hugmarcel · Accepted solution]

Looks like I found the solution myself.

Instead of "rank(sum(figure)) " "rank(sum(figure),2)" must be used in the 2nd column.

egards - Marcel

[sasikanth]

HI

Mode parameter in Rank function helps you on this issue

try some thing like below:

=rank(sum(figure),4)

From help:

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.

=

[HirisH_V7]

Hi,

Check this,

You can use the Expression Like this for varying fields ,

=If(rank(Aggr(Only(figure),dim))=Num#('###,#')-Num#('###,#'),rank(Aggr(Only(figure),dim))+1.5,
rank(Aggr(Only(figure),dim))+1)

Check Attached Rank Splitting app ,

Hope this helps,

PFA

Thanks,

Hirish

[HirisH_V7]

Hi Marcel ,

please Explain that why' 2 'and because its 2 split(4-5) if it is (4-6) means or what ?

Thanks,

Hirish