Monthly Sales Average in QlikSense

[#1]

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

[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!

[#3]

Hi Oleg,

I'm trying like the formula below, but doesn't work:

avg(

AGGR(

Sum({$<GR_CODIGO={$(vGR_VILA)},FORNECEDOR_CODIGO={'39102'}, MES_NOME={'set 2016','out 2016',''nov 2016''}>}VALOR_TOTAL)))

GR_CODIGO = It's a variable that's return just a group of managers;

FORNECEDOR_CODIGO = dimension with a supplier code;

MES_NOME = month + year

VALOR_TOTAL = sales.

[Oleg_Troyansky]

Hi Breno,

it's difficult to guess what might go wrong in your specific case... If you could share a sample app, I would take a look...

cheers,

Oleg Troyansky