Display largest expression value from chart into variables

[#1]

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?

[sunny_talwar · Accepted solution]

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))

[#3]

Hi Sunny,

That worked perfectly.  Thanks for your help.