Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld online is next week! REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

moving average

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.

YearJanFebMarAprilMayJunJulAugSepOctNovDec

Total

2012108121211961010102111130
201311101012121010111281512133
201412911121211910128151286/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!!

3 Replies
marcus_sommer
MVP & Luminary
MVP & Luminary

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

Gysbert_Wassenaar

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


talk is cheap, supply exceeds demand
Not applicable
Author

What I meant was: I have this chart:

voortschrijdend_gemiddelde.png

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.