topic Re: forcast in QlikView

forcast

Wed, 10 Jun 2015 08:58:21 GMT

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:

Year	Month	Rolling avarage
2015	jan	=count(TaskID)=3
2015	feb	=count(TaskID)=6
2015	mrt	=count(TaskID)=6
2015	apr	=count(TaskID)=5
2015	may	=count(TaskID)=9
2015	jun	=count(TaskID)=8
2015	jul	=((5+9+8)/3=7.33
2015	aug	=((9+8+7.33)/3)=8.11
2015	sept	=((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!

Re: forcast

swuehl — Wed, 10 Jun 2015 09:25:27 GMT

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:

Year	Month	Exp
		37
2015	Jan	3
2015	Feb	6
2015	Mar	6
2015	Apr	5
2015	May	9
2015	Jun	8
2015	Jul	7,3333333333333
2015	Aug	8,1111111111111
2015	Sep	7,8148148148148

Re: forcast

Wed, 10 Jun 2015 09:58:38 GMT

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.

Re: forcast

swuehl — Wed, 10 Jun 2015 10:28:11 GMT

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?

Re: forcast

Wed, 10 Jun 2015 10:59:28 GMT

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:

2015	May	9
2015	Jun	8
2015	Jul	8.5
2015	Aug	8.5
2015	Sep	8.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.

Re: forcast

swuehl — Fri, 12 Jun 2015 13:45:54 GMT

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