topic Re: Average of the previous three months in App Development

Average of the previous three months

jerryr125 — Wed, 23 Oct 2019 07:30:21 GMT

Hi -

I have quantity data. I would like to do the following once a month/year is selected (filtered) :

- Take an average of that month and the previous three months

- Display that value in a KPI

I tried this and no luck:

Avg({$<[SalesDate.autoCalendar.YearMonth]={">$(=AddMonths(Max([SalesDate.autoCalendar.YearMonth]),-3,1))<=$(=Max([SalesDate.autoCalendar.YearMonth]))"}>}QuantitySold)

thoughts ? Jerry

Re: Average of the previous three months

marpet — Wed, 23 Oct 2019 08:25:41 GMT

I would like to use a Date field.

Avg({<[SalesDate.autoCalendar.Date]={">$(=MonthStart(AddMonths(Max([SalesDate.autoCalendar.Date]),-3)))<=$(=Max([SalesDate.autoCalendar.Date]))"}, [SalesDate.autoCalendar.Year]=, [SalesDate.autoCalendar.Month]=>}[QuantitySold])

Re: Average of the previous three months

jerryr125 — Wed, 23 Oct 2019 11:31:53 GMT

Hi - unfortunately that did not work.

Example - if I select the following months:

Feb 2016 : Quantity Sold 3,157

May 2016 : Quantity Sold 11,379

April 2016 :Quantity Sold 11,609

The three month average should equal = 8,715

It is displaying: 3.25

thoughts ?

Re: Average of the previous three months

jerryr125 — Wed, 23 Oct 2019 16:02:50 GMT

Hi I tried this expression as well.

It seems to be pulling the correct timeframe BUT not the displaying the average for the last three months (by month).

Thoughts ? Jerry

Avg({$<[SalesDate.autoCalendar.YearMonth]={">$(=AddMonths(Max([SalesDate.autoCalendar.YearMonth]),-3,1))<=$(=AddMonths(Max([SalesDate.autoCalendar.YearMonth]),1,1))"}>}QuantitySold)

Re: Average of the previous three months

jerryr125 — Wed, 23 Oct 2019 17:58:55 GMT

this does the trick :

(Sum({$<[SalesDate]={">=$(=AddMonths(Max([SalesDate.autoCalendar.YearMonth]),-2,1))<$(=AddMonths(Max([SalesDate.autoCalendar.YearMonth]),1,1))"}>}QuantitySold))/3