2 Replies Latest reply: Jun 29, 2016 9:23 AM by Robert Nova RSS

    Display largest expression value from chart into variables

    Robert Nova

      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.

      06-28-2016 - Image 005.png

      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?