Forecasting in Qlikview

[guyvermeiren]

Hello,

I have a linechart with as dimension Year/Period (ex . 201102) and in my expressions Budget and Sales. (see enclosure)

Now for the Sales I would like to make a Forecast (4 up to 6 months).

How can I obtain this. Do I only have to check the box Forecast an put 4 or 6 in it or do I have to put in a formula.

And can I see the values of the forecast ??

Thanks.

Kind regards,

Guy

[johnw]

jbruchon wrote:

However, my point concerns the use of forecast when I have dimension items selected. For example, I put a new field "type" in your script to simulate product category.

Ah, gotcha.  The problem is that the types are only associated with months of ACTUAL sales.  So in the first chart, for instance, you'll still get an appropriate trend line, but it won't be extended to the future months as a forecast.  I should have realized that would happen.

The simplest solution is to just use the built-in linear trend line instead of calculating it yourself as a second expression, but that has limitations of course.

Interestingly, I got an average line to show up for the forecasted months, but not a linear trend line.  Some difference in the two is probably where the problem and solution lies, but I just haven't had time to look into it.  Probably best to just post what I have for now, even if it isn't much.

[swuehl]

Hi John,

I think this issue can be solved by replacing Month abscissa with only({1} Month)

linest_m(total aggr(if(sum(Sales),sum(Sales)),Month),Month)*only({1}Month)

+linest_b(total aggr(if(sum(Sales),sum(Sales)),Month),Month)

See also attached.

Regards,

Stefan

[johnw]

Ah!  Well, now I just feel silly.  Multiply by just the month, which is null for the forecasted months, and of course you're not going to get anything.  Thanks for spotting it. 

[#5]

Hi,

Thanks for your answer. It seems to work perfectly.

Cheers,

Julien

[swuehl]

You're both welcome!

[#7]

Hi,

Thanks for having made this topic going forward.

Cheers

Julien

[Anonymous]

Hi John,

Thanks for all your help in the Qlik Community. Can you please help me solve my forecasting problem in QlikView?

Below are the details:

I am trying to do the forecasting by using the Holt's exponential smoothing. There is a Actual value which is coming from the average of some other values and the formula for the forecast equation in the spread sheet is =[$M$1*B3+(1-$M$1)*(C2+D2)] and for the trend line it is =[$M$2*(C3-C2)+(1-$M$2)*(D2)]...where M1, B3,C2,C3,M2, D2 are the columns.The dimension I am using is Year and I am trying to forecast the value for 2016. Thanks in advance!!!

Pramod

[johnw]

Hi, Pramod. Thank you, but I'm not much active in the community any more. I don't immediately see an answer to your problem, and can't really spend time on it. I'm sorry. You'll probably do better creating a new post for this.

[Anonymous]

No problem John. Thanks!!

[SteveNewman]

Although this thread is fairly old, I believe that  a lot of people are still using it as an example. I leveraged the information in this thread to create a Qlik Sense version of the app and have posted it to github for others to review/improve.

I have heavily leveraged the John and Stefan's expressions and built it for reusability (with variables).

Please feel free to use it as an example, and please provide feedback as well.

GitHub - newmans99/Forecast-Sampler: An example Qlik Sense application that demonstrates forecasting...

Thanks and I hope that this can help a few people.

Steve