3 Replies Latest reply: Dec 19, 2016 7:44 PM by Oleg Troyansky RSS

    Monthly Sales Average in QlikSense

    Breno Gazi

      Hello guys,

       

      I need to create a formula that makes a real average sales of my customers that disregards the months when the customer did not buy anything.

      One option I used in Excel was = SUM (A1: C1) /CONT.SE (A1: C1; "> 0").

      That is, in the above formula it only divides by the amount of months the customer bought.

       

      What would be the way out to create something similar in QlikSense?

       

      Thank you!!

        • Re: Monthly Sales Average in QlikSense
          Oleg Troyansky

          Hi Breno,

           

          I'd experiment with the AGGR() function for this calculation:

           

          avg(

               AGGR(

                         sum(Sales),

                         MonthYear, <any other dimensions as necessary>

               )

          )

           

          This calculation should calculate the real average of the monthly numbers, and I assume that the months with no sales should return NULL and therefore excluded from the calculation - this assumption needs to be verified though...

           

          Upgrade your Qlik skills at the Masters Summit for Qlik - coming up to Munich, Germany in April!