Moving Annual Average

[marsmike]

What's the best way to get MAA on a chart?  MAA is the "Moving Annual Average" and uses prior data that spans more than one year.

So if I want to display on a chart...the last 12 months...it has to be sorted by time, not text?  But the time for the current year is older than the previous year.

The chart would need to display in this order:  Jul, Aug, Sep, Oct, Nov, Dec, Jan, Feb, Mar, Apr, May, Jun.

So it can't be sorted by text, right?  Any ideas?

[#2]

Mike,

If your fiscal year runs from Jul through June you can still sort by month.  First you need to associate the month number with a the month name by doing an inline load.

DisplayMonth:

Load * Inline [

MONTH,MonthName

1, Jul

2, Aug

3, Sep

4, Oct

5, Nov

6, Dec

7, Jan

8, Feb

9, Mar

10, Apr

11, May

12, Jun

];

Next use the AddMonths function to look for anything in the last 12 months.  In the example I am looking for anything with an order date greater than 1 year ago today.  With this formula I can get a running total for the previous 12 months.  You could also just query data for the last 12 months as well.

avg(if(ORDERDATE > addmonths(today(),-12), Amount))