3 Replies Latest reply: May 23, 2016 6:29 AM by Sunny Talwar RSS

    Expression on aggregated data

    Tom Fack

      Dear Qlik community,


      I recently started using Qlik Sense desktop as Excel could no longer handle the amount of data I was feeding it. While I really like the performance I’m struggling a bit with the expressions. Therefore I registered to this community to get some help.


      Please have a look at the visual overview of the requirement I put in attachment. You’ll see I added a stacked bar chart where I already used an expression to display the percentage of each Buying Channel per Year/Month.


      I then added a second chart (= KPI) where I would like to have the average percentage of a sum of Buying Channels displayed. E.g. when Year/Month 2015/01, 2015/02, 2015/03 is selected it displays 25,33% and when only 2015/01 and 2015/02 is selected it displays 25,00%.


      I tried using the following expression: AVG(Count({<[Buying Channel]={Single PO}>}[Buying Channel])+Count({<[Buying Channel]={Low Value}>}[Buying Channel]))


      Unfortunately this expression is not valid. Any ideas?


      P.S. I also attached the qvf-app.





        • Re: Expression on aggregated data
          Sunny Talwar

          Try this expression:


          Avg(Aggr(Sum({<[Buying Channel] = {'eCatalog', 'Mat. Master Contract'}>}Aggr(Round(Count([Buying Channel])/Count(TOTAL <[Year/Month]>[Buying Channel]), 0.01), [Year/Month], [Buying Channel])), [Year/Month]))


          But this has gotten very complicated because of the need to round the numbers to reach what you are looking for.


          If rounding is not a big requirement, then below will work as well:


          Count({<[Buying Channel] = {'eCatalog', 'Mat. Master Contract'}>}[Buying Channel])/Count([Buying Channel]), [Year/Month]))