Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi can someone please assist me.
I'm trying to find the worst selling category using the Rank function in a text object.
I have already done the best selling category using the following :
='The best selling category is: ' & chr(13) & chr(13) & FirstSortedValue(Category,-aggr(sum(Sales),Category))
The following are the categories ordered according to their sales totals
The working formula.
='The worst selling category is: ' & chr(13) & chr(13)
&FirstSortedValue( {<Sales={'>0'}>}Category,aggr(sum(Sales),Category))
It was not working because of a Category(null) with 0 sales. And 0 sales was becoming the worst sales.
Just remove the minus before the aggr:
FirstSortedValue(Category,aggr(sum(Sales),Category))
Hi Swuehl. It still doesn't return anything if I do that
Hi,
PFA, it works with Firstsortedvalue,
thought i have tried out with aggr and concat combination. But the First sorted value solution is preferred.
-Sundar
try below for worst
FirstSortedValue(distinct Category,aggr(sum(Sales),Category))
Solution provided by Stefan should usually work. If there are multiple categories with worst sales, you might want to try with Disrtinct like:
FirstSortedValue( Distinct Category,aggr(sum(Sales),Category))
I have tried to do what you all have suggested and it still doesn't work.
Please see the attached model. It seems that there is a linking issue because when I drop the DateStoreKey it works.
Aggr and concat works, PFA
Thank you it works perfectly. Could you explain your reasoning to it please?
The working formula.
='The worst selling category is: ' & chr(13) & chr(13)
&FirstSortedValue( {<Sales={'>0'}>}Category,aggr(sum(Sales),Category))
It was not working because of a Category(null) with 0 sales. And 0 sales was becoming the worst sales.