Forecasting with zero-values

Mon, 21 Mar 2016 16:46:36 GMT

Hi,

I have a question regarding a forecast I want to make in Qlikview:

I want to forecast the price of the upcoming months based on the costs of the previous months and based on some forecast weight parameters:

The expression I used:

(Sum( aggr( rangesum( above(Costs,0,12) ),Month)) - Costs) / (Sum( aggr( rangesum( above(ForecastFactor,0,12) ),Month)) - ForecastFactor) * ForecastFactor

The problem starts in month 8. If the value is € 0,00, the costs for that month are still not known. So, for month 8 I do not want to take the costs of month 7 into account, as the costs are still €0,00. However, with my expression it goes wrong: the forecast of month 8 is based on the costs of month 1 to 7 divided by the forecastfactor s of those months, instead of being based on the costs and forecastfactors of month 1 to 6. In fact I expect for the months 8, 9, 10 and 11 a forecast of € 1,75 as the forecastfactor is 8 and the forecast is based on the real costs of month 1 to 6.

Moreover, I prefer to solve this problem using the expressions and/or other chart properties. Anyone knows how I can base the forecast costs of month 8 on the output of month 1 to 6 instead of 1 to 7?

Thank you in advance!

Re: Forecasting with zero-values

swuehl — Mon, 21 Mar 2016 17:51:35 GMT

Maybe something like

(Sum( aggr( rangesum( above(Costs,0,12) ),Month)) - Costs) / (Sum( aggr( rangesum( above(Only({<Costs = {">0"}>}ForecastFactor),0,12) ),Month)) - ForecastFactor) * ForecastFactor

Maybe add the Only() with set expression also to your other ForecastFactor references.

topic Forecasting with zero-values in QlikView

Forecasting with zero-values

Re: Forecasting with zero-values