topic Re: moving average in QlikView

moving average

Thu, 14 Aug 2014 09:18:11 GMT

For a management chart I need (what the manager calls) a moving average. He wants to see the amount of contacts per time zone. Let’s say we take year. Than he wants the amount of contacts in 2012, 2013 and 2014. the first 2 are no problem. I can show the contacts of 2012 and 2013. I can also show the contacts 2014 but that is only the months January until august because September until December is in the future. Now I need to show a moving average that fills the gap of September until December with the same months of 2013.

Year	Jan	Feb	Mar	April	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec	Total
2012	10	8	12	12	11	9	6	10	10	10	21	11	130
2013	11	10	10	12	12	10	10	11	12	8	15	12	133
2014	12	9	11	12	12	11	9	10	12	8	15	12	86/133

The red numbers are from the months of 2013 so 2014 has 12 months of data instead of 8.

Is there a way in Qlikview how I can manage this in a table and/or a chart?

Thanks to everybody for helping!!

Re: moving average

marcus_sommer — Thu, 14 Aug 2014 11:08:10 GMT

I use the following expression to calculate a linear year-end forecast for the current year but I think you could adapt it for your request. In your case you could also look for inter-record functions like above() or before() to return the results from other rows/columns.

if(Monat <= max({1< Jahr = {$(=year(today() -1))}>} Monat),

sum({ < Jahr = {$(=year(today() - 1))} >} Menge),

sum({1 < Jahr = {$(=year(today() -1))} >} Menge) /

max({1< Jahr = {$(=year(today() -1))}, Menge = {">=0<=0"}>} total Monat) * (1 + vHRFaktor))

An alternatively could be to use for the future time-periods the planning-data.

- Marcus

Re: moving average

Gysbert_Wassenaar — Thu, 14 Aug 2014 12:23:02 GMT

See attached qvw. Not sure what you want to do with regards to the moving average. Perhaps this document helps: Calculating rolling n-period totals, averages or other aggregations

Re: moving average

Thu, 14 Aug 2014 13:57:28 GMT

What I meant was: I have this chart:

Red =Count (consults)

Blue =Count({<Year={$(=Year-1)}>} consults)

My problem is that I have only 3 months of data from 2014. My average ("gemiddelde", the red line) is very low because of the other 9 months being 0.

I want this chart to show the rest of the months a forecast. Ether with a forecasting solution or with a moving average solution.