topic Re: Ranking shows from 2 in QlikView

Ranking shows from 2

renjithpl — Fri, 11 Oct 2013 09:34:57 GMT

Hi All,

I used rank function, and suppressed zeros, but my rank shows 2 onwards.

aggr(rank(Sum({<Day=, Date={'>=$(=monthstart(Max(Date))) <=$(=max(Date))'} >} [Daily Margin])/1000, [CMG Name])))

Regards

Ren

Re: Ranking shows from 2

jonathandienst — Fri, 11 Oct 2013 09:40:55 GMT

I think you may have a tie for first place. Experiment with the mode and format optional parameters, as in

rank(expression, mode, format)

Mode can take a value from 0 - 4 and controls how ties are handled. Format can take a value from 0 - 2 and controls how the rank is displayed.

Regards

Jonathan

Re: Ranking shows from 2

tresB — Fri, 11 Oct 2013 09:46:05 GMT

"Note: Suppression of zero values is automatically disabled when these functions are used." - This is what qv help says. If this does not explain your issue, request to share sample app. May be rank ties are there.

Re: Ranking shows from 2

renjithpl — Fri, 11 Oct 2013 10:54:58 GMT

Hi Jonathan,

Is there any way, i can bring in serial number in pivot table ?

Apparently, i need to show the numbers from 1 to ...N, i have already sorted based on the expression.

So, the serial number will tell the user who is on top, and which number that particular line item belong to.

Regards

Ren

Re: Ranking shows from 2

tresB — Fri, 11 Oct 2013 11:04:38 GMT

You can try RowNo() function. TOTAL keyword might be required if you have multiple dimensions

Re: Ranking shows from 2

renjithpl — Sat, 12 Oct 2013 13:05:37 GMT

Thanks for the answers,

But I did this,

Eg:

=if(aggr(rank(sum(Sales)),[CMG Name])<=2,1, aggr(rank(Sum(Sales)), [CMG Name])-1)

It works as i want, may be its a temporary solution

Regards

Ren