Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Announcements
Save the Date: QlikWorld Online, June 24-25, 2020. Free global virtual event for data integration and data analytic gurus. Register Today
Highlighted
Not applicable

forcast

Hello,

I hope somebody can help me with this. A customer of mine wants to see a rolling avarage (as he calls it) into the future.

An example of his definition of a rolling avarage:

YearMonthRolling avarage

2015

jan=count(TaskID)=3
2015feb=count(TaskID)=6
2015mrt=count(TaskID)=6
2015apr=count(TaskID)=5
2015may=count(TaskID)=9
2015jun=count(TaskID)=8
2015jul=((5+9+8)/3=7.33
2015aug=((9+8+7.33)/3)=8.11
2015sept=((8+7.33+8.11)/3)=7.81

As you see the customer wants that when there aren't any TaskID values the Rolling avarage of the last periode is taken. In this way he can "predict" future task capacity.

I hope this is posible. Thanks in advance!

5 Replies
Highlighted
MVP
MVP

Re: forcast

It's probably more easy to calculate in the script, but I assumed you want a chart based solution.

Maybe like attached?

edit: Updated with your values, result:

YearMonthExp
37
2015Jan3
2015Feb6
2015Mar6
2015Apr5
2015May9
2015Jun8
2015Jul7,3333333333333
2015Aug8,1111111111111
2015Sep7,8148148148148
Highlighted
Not applicable

Re: forcast

Thank you for your solution! This is most of what i need. The problem is when i select a quarter or a couple of months or 1 year (when i have data from more years) the expression isn't working anymore. How can i still have the rolling avarage over three months in a selection?

Ofcourse the dimensions of the table (or chart) should react to the selection and only show the values of the months, quarters or years i selected. But the calculation within the expression shouldn't react to the selection.

Highlighted
MVP
MVP

Re: forcast

Selecting Months, like Apr to Sep, doesn't break my table as far as I see. Of course the forecasted months needs to be part of the selection.

Could you give a more specific example of how your data looks like and what you want to see when making specific selections?

Highlighted
Not applicable

Re: forcast

If you make the selection Apr to Sep it does stay the same. But what if you make the selection May to Sep, the table will be as followed:

2015May9
2015Jun8
2015Jul8.5
2015Aug8.5
2015Sep8.33

And i still want to see the values with the three month rolling avarage and not changing to two or three months as it did in the selection i made.

MVP
MVP

Re: forcast

Here is a different approach:

Assuming the number of forecasted month is limited to three, we can pre-calculate the forecasted values and assign to three variables we can then use in the chart expression.

This makes it selection insensitive (well, at least you can control via the variable expressions which selections you want to be considered and which you don't).