topic Re: Display largest expression value from chart into variables in QlikView

Display largest expression value from chart into variables

Tue, 28 Jun 2016 17:05:16 GMT

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?

Re: Display largest expression value from chart into variables

sunny_talwar — Tue, 28 Jun 2016 20:01:58 GMT

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

Re: Display largest expression value from chart into variables

Wed, 29 Jun 2016 13:23:42 GMT

Hi Sunny,

That worked perfectly. Thanks for your help.