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:

          Avg(Aggr(

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

           

          Capture.PNG

           

          Best,

          Sunny