Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Not applicable

Display largest expression value from chart into variables

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?

1 Solution

Accepted Solutions

Re: Display largest expression value from chart into variables

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

2 Replies

Re: Display largest expression value from chart into variables

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

Not applicable

Re: Display largest expression value from chart into variables

Hi Sunny,

That worked perfectly.  Thanks for your help.

Community Browser