forcast

[#1]

Hello all,

I have a problem with my qv document. For some kind of forcast i need to get values from precious months. I want to calculate the next month via an evarage from the previous 12 months. But the problem is that i cant grab a count() value before januari becouse for some reason my QV document isn't seeing December above januari.

I've added an example in which i can't get a value fromd december in the month januari.

Can anybody help me with this?

Thanks in advance!!

[swuehl]

Above() is considering column segments, you need to use the TOTAL qualifier to get what you want:

=above(TOTAL count(TaskID),1)

[Gysbert_Wassenaar]

Since you're using more than one dimension in your chart you need to add the total keyword to the above function so it will be able to cross the dimension borders: above(total count(TaskID),1)

As an aside... the above function can only work with data that is in the chart context. If for example you select 2014 then your expression can't get at the values in 2013. This document can show you how to get around that issue: Calculating rolling n-period totals, averages or other aggregations

[#4]

Thank you very much! This helps. Although i still keep left with one problem with which you could help me.

My customer wants to see a rolling avarage (as he calls it) into the future.

For example if we take the table from the file i added:

Year	month	count(TaskID)	Rolling avarage
2013	dec	3	-
2014	jan	6	-
2014	feb	6	-
2014	mrt	-	=((3+6+6)/3)=5
2014	apr	-	=((6+6+5)/3)=5.67
2014	may	-	=((6+5+5.67)/3)=5.56

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!

[Gysbert_Wassenaar]

That's a tricky one. I've come up with something that looks like it will work in certain situations. See attached qvw. Adding additional dimensions will play havoc with it. And so can selections.

[MarcoWedel]

Hi,

another solution as well as some alternative "rolling average" definitions could be also:

hope this helps

regards

Marco

[MarcoWedel]

any luck testing the proposed solutions so far?

thanks

regards

Marco

[#8]

Hello Marco,

Sorry for the late reaction. But i'm still working things out. It isn't working properly but all the help gave me a good nodge in the richt direction.

The only thing i need to fix now (i thing) is how to use a calculated label in an expression.

I can already use a label in an expression bur my problem is what if the label is "=count(TaskID)" how can i use that in my expression?

[MarcoWedel]

Hi,

you can reference to other columns within a chart not only by column name (expression label) but also using the Column() function (see help).

hope this helps

regards

Marco