Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have a requirement that the user wants to be able to see the category that has the largest growth Year on Year for the given period, I am trying to put this into a variable so that I can print the value out in an NPrinting template. I was able to achieve the largest Year on Year growth into a text object by using the below:
= num(max(aggr((Sum ({$<Retailer=P(Retailer_MKT), RecordType={'N'}, Year={$(vCY)}, YearWeek={"<=$(=vWeekCY)"}, Month=, Quarter=>} SalesValue)
- Sum ({$<Retailer=P(Retailer_MKT), RecordType={'N'}, Year={$(vPY)}, YearWeek={"<=$(=vWeekPY)"}, Month=, Quarter=>} SalesValue)) /
Sum ({$<Retailer=P(Retailer_MKT), RecordType={'NIELSEN'}, Year={$(vPY)}, YearWeek={"<=$(=vWeekPY)"}, Month=, Quarter=>} SalesValue), ProductCategory)),'##.#0%')
But that leaves me with the number in this case 6% growth, but what I need is to get the corresponding Category name into a variable as well. I see the values in a pivot chart, but I am trying to get the largest value into a variable.
In my chart above, I would want two variables, one variable ='Personal Care' and the other with ='6.00%'
What is the best way to achieve this?
How about this:
=FirstSortedValue(ProductCategory, -Aggr((Sum ({$<Retailer=P(Retailer_MKT), RecordType={'N'}, Year={$(vCY)}, YearWeek={"<=$(=vWeekCY)"}, Month=, Quarter=>} SalesValue)
- Sum ({$<Retailer=P(Retailer_MKT), RecordType={'N'}, Year={$(vPY)}, YearWeek={"<=$(=vWeekPY)"}, Month=, Quarter=>} SalesValue)) /
Sum ({$<Retailer=P(Retailer_MKT), RecordType={'NIELSEN'}, Year={$(vPY)}, YearWeek={"<=$(=vWeekPY)"}, Month=, Quarter=>} SalesValue), ProductCategory))
How about this:
=FirstSortedValue(ProductCategory, -Aggr((Sum ({$<Retailer=P(Retailer_MKT), RecordType={'N'}, Year={$(vCY)}, YearWeek={"<=$(=vWeekCY)"}, Month=, Quarter=>} SalesValue)
- Sum ({$<Retailer=P(Retailer_MKT), RecordType={'N'}, Year={$(vPY)}, YearWeek={"<=$(=vWeekPY)"}, Month=, Quarter=>} SalesValue)) /
Sum ({$<Retailer=P(Retailer_MKT), RecordType={'NIELSEN'}, Year={$(vPY)}, YearWeek={"<=$(=vWeekPY)"}, Month=, Quarter=>} SalesValue), ProductCategory))
Hi Sunny,
That worked perfectly. Thanks for your help.