Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
If you are a Qlik Insight Bot user, join this collaborative group: JOIN GROUP
Highlighted
Contributor

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

Forecasting in Qlikview

I haven't used the forecast feature, but quickly poking at it, it looks like you tell it what KIND of forecast you want by specifying what sort of trend line to use on the Expressions tab for that expression. Then on the Axes tab, you tell it to forecast and how many data points to forecast.

Now on to your questions - what sort of answer do you want? Put in a 4 if you want 4 months forecast. Put in a 6 if you want 6 months forecast. If you want the number of months to forecast to be calculated, enter the calculation you want.

No, you can't see the values for the forecast, which I discovered by asking for a forecast. That didn't surprise me since you don't get to see values for a trend line either.

If you want a linear trend line for your forecast, and you want to see the values, and you only want the forecast where you don't have the data, you could do something like the attached. I think the third chart is closest to what you're asking for, but they all use the same basic approach of calculating a linear trend line themselves.

Highlighted
Not applicable

Forecasting in Qlikview

hi John, I have set up a forecast using your testLineEquation.qvw and all is working ok - thanks!!

I'm a little confused as to how the calculations/formula works.. could you explain?


In the below table, I have the initial figure calculated at "205"... how does it get this figure?

thanks!

MONTHSUM(SALES)FORECAST
R 1,150.00R 0.00DIFFERENCE
1R 100.00R 205.00205
2R 500.00R 260.0055
3R 150.00R 315.0055
4R 400.00R 370.0055
5R 0.00R 425.0055
6R 0.00R 480.0055
7R 0.00R 535.0055
8R 0.00R 590.0055
9R 0.00R 645.0055
10R 0.00R 700.0055
11R 0.00R 755.0055
12R 0.00R 810.0055
Highlighted
MVP
MVP

Re: Forecasting in Qlikview

The forecast should be a linear regression based on the data points you have.  There are various definitions of a linear regression, and I don't know which one QlikView is using, so I can't tell you exactly how it calculated 205.  I believe the most common is the least sum of squares, which I believe is to say the trend line where if you sum the square of distances of each point to the trend line, you get the lowest possible value.  An overview of various calculations is available on wikipedia.

http://en.wikipedia.org/wiki/Linear_regression

If you were more asking what the expression does...

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

The first if() says that if we have actual data, we use it instead of the forecast.  Linest_m calculates a slope, and linest_b calculates a Y-intercept.  The "total" keyword is used because we need to ignore the specific month for that data point and calculate a linear regression for all months.  The if() inside of the aggr()s makes sure we only include actual data points in our calculation (else we would include 0 for any missing month in our calculation, which would be wrong).  The aggr() gives us the set of data points by month, and the linest_x(...,Month) says that Month is our X-axis for the linear regression.

Highlighted
Not applicable

Forecasting in Qlikview

Thanks for the response John. I have the same table above with the inner if() removed in a second column and I can see the equation takes the full month into account and doesnt give an accurate forecast

MONTHSUM(SALES)FORECAST (IF)FORECAST (no IF)DIFF #1DIFF #2
R 1,150.00R 0.00R 0.00
1R 100.00R 205.00R 262.18R 205.00R 262.18
2R 500.00R 260.00R 231.93R 55.00R -30.24
3R 150.00R 315.00R 201.69R 55.00R -30.24
4R 400.00R 370.00R 171.45R 55.00R -30.24
5R 0.00R 425.00R 141.20R 55.00R -30.24
6R 0.00R 480.00R 110.96R 55.00R -30.24
7R 0.00R 535.00R 80.71R 55.00R -30.24
8R 0.00R 590.00R 50.47R 55.00R -30.24
9R 0.00R 645.00R 20.22R 55.00R -30.24
10R 0.00R 700.00R-10.02R 55.00R -30.24
11R 0.00R 755.00R-40.27R 55.00R -30.24
12R 0.00R 810.00R-70.51R 55.00R -30.24


My concern is if there is a month (e.g. month 5) that does result in 0.00 Sales, and they make sales in month 6 (e.g. 500), The forecast would not take month 5 into its calculations correct?

Would it not be easier creating an average (avg(total(Sales))) as the forecast? Do you maybe have a formula for this?

Thanks again!

Highlighted
MVP
MVP

Re: Forecasting in Qlikview

The inner if() is removing months with 0, so it would remove month 5, yes.  Without it, you're including months 7-12.  There are other solutions for the inner if(), though.  For instance, doing something like if(Month<=monthstart(today()),sum(Sales)), and putting a similar condition on the outer if.  It all depends on your requirements.

Yes, an average is a little easier than a linear trend.  For instance, in the third chart, I could do this:

if(Month<=month(today()),sum(Sales),avg(total aggr(if(Month<=month(today()),sum(Sales)),Month)))

But you still need the inner if() to avoid counting your forecast months as 0 when calculating the average.  And you still need the outer if() to make sure your trend line starts from the last point of real data.  And what are your requirements for the forecast?  If your users want a linear trend, use a linear trend.  If they want an average, use an average.  If they want some calculation specific to their business, use that calculation.  I can't talk to your users, so I can't tell you what you need to do.

Highlighted
Not applicable

Forecasting in Qlikview

Thanks for this John. The users want an average, and was just needing a bit of help on the formula for that. Thanks again man!

Highlighted
Partner
Partner

Re: Forecasting in Qlikview

Hi:

I like the way you placed it in the graphic.

I´d like to add some. Time series analysis is not only about the trend analysis. They are also cyclic component, stationary component and random component, and that would be the classic time series analysis.

I think I posted some file with that Time Series Analysis long ago but my files disappeared along with my points in the last site remodeling.

Highlighted
MVP
MVP

Forecasting in Qlikview

Everyone's points were recalculated in the move to the new forum, but I don't think files were supposed to disappear.  I could be wrong.

Highlighted
New Contributor II

Re: Forecasting in Qlikview

Hi John - I've been asked to do some forecasting with Qlikview and came accross this post while researching.  I've borrowed your example above using the linest b and linest m functions and it works perfectly if I only load 1 years worth of data.  Is there a reason this only works with 1 year of data or perhaps I'm doing something wrong?

Thanks,

Simon