Skip to main content
Announcements
See what Drew Clarke has to say about the Qlik Talend Cloud launch! READ THE BLOG
cancel
Showing results for 
Search instead for 
Did you mean: 
guyvermeiren
Creator
Creator

Forecasting in Qlikview

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

32 Replies
johnw
Champion III
Champion III

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
MVP
MVP

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
Champion III
Champion III

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. 

Not applicable

Hi,

Thanks for your answer. It seems to work perfectly.

Cheers,

Julien

swuehl
MVP
MVP

You're both welcome!

Not applicable

Hi,

Thanks for having made this topic going forward.

Cheers

Julien

Anonymous
Not applicable

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
Champion III
Champion III

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
Not applicable

No problem John. Thanks!!

SteveNewman
Employee
Employee

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