4 Replies Latest reply: Jan 19, 2018 8:33 AM by Sunny Talwar

# Rank Caluculation Using aggr() functions

Hi all,

I am calculating rank in a table. my report looks like after selecting a dimension like below.

 X Y YTD sales XY Rank Y rank 9000 1 19 8000 2 27 7000 5 83 6000 5000

Rank calculation is done based on this below expression.

XY Rank = Aggr(Nodistinct rank(sum({\$<[Process Date]={">=\$(=YearStart(Max([Process Date]))) <=\$(=Max([Process Date]))"},

[Month ID]=>}[Sales Amt]),4),X,Y).

Y Rank=aggr(Nodistinct rank(sum({\$<[X]=,[Month ID]=,[Process Date]={">=\$(=YearStart(Max([Process Date]))) <=\$(=Max([Process Date]))"}>}[Sales Amt])),[Y]).

But the problem is when I am selecting other dimension like Month Id the rank calculation is not based on this particular month so by selecting a particular month rank is showing 1,2,3,6,9,12, like this as other position not part in this month so they removed.

But I want continuous rank based on each dimension selection.

• ###### Re: Rank Caluculation Using aggr() functions

When I am not selecting Month Id reports looks good. But when selecting any dimension like Month id its not showing desired result.

• ###### Re: Rank Caluculation Using aggr() functions

Which of the two isn't working? XY or Y?

• ###### Re: Rank Caluculation Using aggr() functions

Both of them

• ###### Re: Rank Caluculation Using aggr() functions

Would you be able to share a sample where we can see the issue.... and also if you can share the output you expect to see based on the selection... then that would be great