topic Re: Average month sales in QlikView

Average month sales

sagaraperera — Tue, 06 Oct 2015 06:18:38 GMT

to calculate average of period sum sales amount. i'm looking for some examples and i've found agg but it is not working properly.my script have a year and month field, I select a month of September then I want a past 09 month totals average sales.please help

Re: Average month sales

sujeetsingh — Tue, 06 Oct 2015 06:22:26 GMT

follow these thread

Aggregate Average?

Aggr Function

try understanding the way aggr() function works follow the reference manual.

Re: Average month sales

MK_QSL — Tue, 06 Oct 2015 06:27:13 GMT

Create a field InvoiceMonthYear as below in your script

Date(MonthStart(InvoiceDate),'MMM-YY') as InvoiceMonthYear,

Now use InvoiceMonthYear field in Listbox filter.

=Avg(Aggr(SUM({<InvoiceYear=, InvoiceMonth=, InvoiceQuarter=, InvoiceDate = {">=$(=MonthStart(Max(InvoiceMonthYear),-9))<=$(=MonthEnd(Max(InvoiceMonthYear),-1))"}>}Sales),InvoiceMonthYear))

The above expression will give you Avg Sales per Last 9 months..

if you select Oct-15, it will give you Avg sales for Jan-15 to Sep-15.

But if you want sales for Feb-15 to Oct-15, use below

=Avg(Aggr(SUM({<InvoiceYear=, InvoiceMonth=, InvoiceQuarter=, InvoiceDate = {">=$(=MonthStart(Max(InvoiceMonthYear),-8))<=$(=MonthEnd(Max(InvoiceMonthYear),0))"}>}Sales),InvoiceMonthYear))

Change field names accordingly...

Re: Average month sales

engishfaque — Tue, 06 Oct 2015 09:30:02 GMT

Dear Sanka,

=Avg(Aggr(Sum (Sales), Year, Month))

Kind regards,

Ishfaque Ahmed