Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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])
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 ?
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)
this does the trick :
(Sum({$<[SalesDate]={">=$(=AddMonths(Max([SalesDate.autoCalendar.YearMonth]),-2,1))<$(=AddMonths(Max([SalesDate.autoCalendar.YearMonth]),1,1))"}>}QuantitySold))/3