Ranking shows from 2

[renjithpl]

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

[jonathandienst]

Hi

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

[tresB]

"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.

[renjithpl]

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

[tresB]

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

[renjithpl · Accepted solution]

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