Moving Annual Average

marsmike — Tue, 05 Jul 2011 18:20:26 GMT

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?

Re: Moving Annual Average

Tue, 05 Jul 2011 19:35:22 GMT

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

topic Re: Moving Annual Average in QlikView

Moving Annual Average

Re: Moving Annual Average