Announcements
cancel
Showing results for
Did you mean:
Creator III

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

1 Solution

Accepted Solutions
MVP

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)

4 Replies
MVP

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?

Creator III
Author

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.

MVP

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)

Creator III
Author

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

Community Browser