rank() with aggr() gies me '-', instead of rank, please help

[ananyaghosh]

Hi,

I am using the below set analysis code to get the ranking based on Prior Year YTD:

aggr(Nodistinct rank(sum({$<[Process Year],[Process Date]={">=$( =YearStart(AddYears(Max([Process Date]),-1))) <=$(=AddYears(Max([Process Date]),-1))"}>}[Sales Posting Amount USD])),[Firm Name])

and it gives me '-' for all the rows.

Now you can see that YTD calculation for prior year is:

sum({$<[Process Year],[Process Date]={">=$( =YearStart(AddYears(Max([Process Date]),-1))) <=$(=AddYears(Max([Process Date]),-1))"}>}[Sales Posting Amount USD])) and it is 0 for all the rows.

So my rank set analysis should give me 1 , means same rank instead of showing '-' character.

What should I do now?

Thanks,

Sandip

[sunny_talwar]

You have 0 sales? Why would you want to rank 0 sales firm? or am I missing something here? Can you share a sample to demonstrate your issue here?

[ananyaghosh]

Hi,

You can see that column 'Prior YTD Sales' have 0 values , so my client wants that instead of showing '-', show 1 for all rows if it has 0 YTD sales for all the rows of 'Prior YTD Sales' column.

So my requirement is : for 'Prior YTD RANK' column , it show 1, if all the rows of 'Prior YTD Sales' is 0, but other tan that rank will be shown as usual.

[sunny_talwar · Accepted solution]

Not very clear, but may be do this

Alt(aggr(Nodistinct rank(sum({$<[Process Year],[Process Date]={">=$( =YearStart(AddYears(Max([Process Date]),-1))) <=$(=AddYears(Max([Process Date]),-1))"}>}[Sales Posting Amount USD])),[Firm Name]), 1)

[ananyaghosh]

Hi,

I have used the alt() function here, and it is showing correct. You give me very simple and correct solution to me.

Thanks,

Sandip