topic Calculate with split ranks in QlikView

Calculate with split ranks

hugmarcel — Wed, 30 Dec 2015 08:31:22 GMT

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

Re: Calculate with split ranks

tresB — Wed, 30 Dec 2015 08:37:54 GMT

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?

Re: Calculate with split ranks

hugmarcel — Wed, 30 Dec 2015 08:41:19 GMT

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

thx - Marcel

Re: Calculate with split ranks

hugmarcel — Wed, 30 Dec 2015 08:50:22 GMT

I have now attached the sample. see there

Re: Calculate with split ranks

hugmarcel — Wed, 30 Dec 2015 09:07:01 GMT

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

Re: Calculate with split ranks

sasikanth — Wed, 30 Dec 2015 09:09:43 GMT

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.

Re: Calculate with split ranks

HirisH_V7 — Wed, 30 Dec 2015 09:33:23 GMT

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

Re: Calculate with split ranks

HirisH_V7 — Wed, 30 Dec 2015 09:40:11 GMT

Hi Marcel ,

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

Thanks,

Hirish