Average month sales

[sagaraperera]

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

[sujeetsingh]

follow these thread

Aggregate Average?

Aggr Function

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

[MK_QSL]

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

[engishfaque]

Dear Sanka,

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

Kind regards,

Ishfaque Ahmed