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

# Monthly Sales Average in QlikSense

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

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!

• ###### Re: Monthly Sales Average in QlikSense

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.

• ###### Re: Monthly Sales Average in QlikSense

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