topic Re: Moving average trend calculation. in QlikView

Moving average trend calculation.

Anonymous — Sat, 21 Feb 2015 09:11:07 GMT

Hi all...I need to show a trend of current month sales vs previous 3 month moving average sales excluding the current month for a selected year in line chart irrespective of user selection. For example, if current month sales for the month of July in the year 2014 is 2000, average sales of previous 3 months(April,May,June) is to be shown against July and likewise for all the months.

Can anybody please help??

Re: Moving average trend calculation.

robert_mika — Sun, 22 Feb 2015 06:03:23 GMT

Could you post sample of your data?

Re: Moving average trend calculation.

Anonymous — Sun, 22 Feb 2015 07:25:34 GMT

This is the sample data. Please note that the month for which the avg sales is being calculated, is excluded and only its previous 3 month sales is taken into consideration.

Year	Month	Sales	Previous 3 month Avg Sales
2014	1	2000	0
2014	2	3000	2000
2014	3	1000	1667
2014	4	2500	2000
2014	5	4000	2167
2014	6	3000	2500
2014	7	1000	3167
2014	8	2000	2667
2014	9	3500	2000
2014	10	4000	2167
2014	11	1500	3167
2014	12	2000	3000

Re: Moving average trend calculation.

robert_mika — Sun, 22 Feb 2015 10:40:30 GMT

If that the case and 3 last month are taken into account so month 3 should be 2500 not 1667,

Please see below

The Expression is

RangeAvg(Above(sum(Sales),1,3))

Re: Moving average trend calculation.

MK_QSL — Sun, 22 Feb 2015 10:47:52 GMT

Excellent and Useful blog created by Gysbert...

Calculating rolling n-period totals, averages or other aggregations

Re: Moving average trend calculation.

Anonymous — Mon, 23 Feb 2015 06:32:09 GMT

Thanks Robert, It's working absolutely fine.

Re: Moving average trend calculation.

Anonymous — Mon, 23 Feb 2015 06:32:45 GMT

Thanks Manish, that was helpful.

Re: Moving average trend calculation.

robert_mika — Mon, 23 Feb 2015 08:20:32 GMT

You are welcome.