topic Re: Forecasting in Qlikview in QlikView

Forecasting in Qlikview

guyvermeiren — Mon, 28 Feb 2011 16:25:49 GMT

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

Forecasting in Qlikview

johnw — Mon, 28 Feb 2011 23:38:01 GMT

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.

Forecasting in Qlikview

Wed, 24 Aug 2011 12:27:13 GMT

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!

MONTH	SUM(SALES)	FORECAST
	R 1,150.00	R 0.00	DIFFERENCE
1	R 100.00	R 205.00	205
2	R 500.00	R 260.00	55
3	R 150.00	R 315.00	55
4	R 400.00	R 370.00	55
5	R 0.00	R 425.00	55
6	R 0.00	R 480.00	55
7	R 0.00	R 535.00	55
8	R 0.00	R 590.00	55
9	R 0.00	R 645.00	55
10	R 0.00	R 700.00	55
11	R 0.00	R 755.00	55
12	R 0.00	R 810.00	55

Re: Forecasting in Qlikview

johnw — Wed, 24 Aug 2011 17:09:47 GMT

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.

Forecasting in Qlikview

Thu, 25 Aug 2011 06:38:18 GMT

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

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

Re: Forecasting in Qlikview

johnw — Thu, 25 Aug 2011 17:01:17 GMT

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.

Forecasting in Qlikview

Sun, 28 Aug 2011 16:19:39 GMT

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!

Re: Forecasting in Qlikview

christian77 — Mon, 29 Aug 2011 06:48:33 GMT

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.

Forecasting in Qlikview

johnw — Mon, 29 Aug 2011 15:03:54 GMT

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.

Re: Forecasting in Qlikview

Anonymous — Thu, 27 Oct 2011 14:21:48 GMT

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

Re: Forecasting in Qlikview

johnw — Fri, 04 Nov 2011 00:24:31 GMT

If your months are defined like month(Date), then the underlying values are just 1-12, and when you have more than one year of data, that would probably cause problems. I typically define months like date(monthstart(Date),'MMM YYYY'). I have no idea if the actual problem you're having has anything to do with this, though. It's just the first thing that occurred to me that might cause problems when you have more than one year of data.

Re: Forecasting in Qlikview

Anonymous — Wed, 09 Nov 2011 18:41:55 GMT

Thanks John. I'm actually not using months as we use fiscal periods and have 13 per calendar year. But I'm guessing the same problem would exist since I'm just loading the period # as 1-13. Not sure how I would get around this as the fiscal period is numeric by default - perhaps if I renamed the periods as P1, P2 etc, thereby making them text?

Re: Forecasting in Qlikview

johnw — Wed, 09 Nov 2011 18:58:13 GMT

OK, I assume you're using four week periods, then? I'd define a new field which is the period combined with the year. You can use whatever you want as the display. I'd probably use something like 'P7 2011'. But beneath that text, use a dual() function to assign the field the starting date of the period. As an example, we have 52 payroll weeks per year (and so our payroll year starts a little earlier every year). Here's some of the code from where I create a calendar QVD that includes this data:

[Calendar]:
LOAD *
,dual('W' & "Payroll Week Number Only" & ' ' & "Payroll Week Belongs to Year","Week") as "Payroll Week Number"
;
LOAD *
,date(weekstart("Date",0,-1)) as "Week"
,mod( floor(("Date"-makedate(2010,12,19))/7),52)+1 as "Payroll Week Number Only"
,2011+floor(("Date"-makedate(2010,12,19))/364) as "Payroll Week Belongs to Year"
;
LOAD date(today() + 10000 - recno()) as Date
AUTOGENERATE 20000
;

Now the payroll week number displays like 'W27 2011', but the actual value of the field is a week starting date. Since it's a full date, we can use all the date functions on it, and we can create a trend line that spans multiple years if desired.

Re: Forecasting in Qlikview

Anonymous — Wed, 09 Nov 2011 19:27:16 GMT

Thanks again John. Well, not exactly - our fiscal year runs April 1 to March 31 and after inspection I see that the first and last periods of each year typcially don't have 4 weeks in them (usually 3 and 5 respectively, or somewhere in between). The rest of the periods each year do have 4 weeks to each. To make it worse it changes from fiscal year to year, P1 of 2011 has 23 days, in 2010 it had 24 days.

This wouldn't be the first time our fiscal calendar has created challanges for me. Unfortunately I'm not exactly a pro with Qlikview so I'll have to research your code above to understand it more clearly, although I wonder with my strange fiscal calendar I'll even be able to use a similar idea.

Re: Forecasting in Qlikview

johnw — Wed, 09 Nov 2011 19:40:24 GMT

With your uneven intervals, what I said probably won't be what you want for a trending line. In that case, I'd do similar, but assign a numeric instead of a date. Something like this:

LOAD *
,dual('P' & "Period" & ' ' & "Period Belongs to Year","Period Belongs to Year"*13 + "Period" - 1) as "Period and Year"
;

LOAD
...

,some crazy expression as "Period"
,some related crazy expression as "Period Belongs to Year"

...

The actual number being assigned isn't critical, just that it increments evenly for every period. The dual() then gives you a field to do linear regression on while still displaying the field value in a sensible way.

Forecasting in Qlikview

Wed, 07 Mar 2012 22:56:07 GMT

Hey John,

Is there any way to utilize a multi-variable regression with QV? For example, using the LOBF:

Total Sales = B(0) + B(1)*(Month) + B(1)*(Discount) + B(2)*(# of Competitor Brands)

How would I:

1. Display the forecasted amounts on a chart

2. Use an input box in order to shift the variables in order to modify my forecasts

Thanks,

Jon

Re: Forecasting in Qlikview

Wed, 13 Jun 2012 16:00:31 GMT

Hi John,

I really like your response.

I'd tested it and it works well as long as I don't select any dimension items. Then my forecasts disappear.

Do you know how to make your formula works whatever selection is made ?

Thanks for your help

Julien

Re: Forecasting in Qlikview

johnw — Wed, 13 Jun 2012 19:09:06 GMT

I'm not quite sure what you're saying. Do you mean, for instance, that if you select month 6 in my posted example, the forecast disappears? It disappears from the bottom two charts because we have real data for month 6, so don't forecast it. It disappears from the top chart because you can't build a linear trend line from a single data point. If you select months 6 and 7, you'll get a linear trend line based on those two months, which will simply exactly match the values for those two months. If you select more months, you would see a linear trend line appropriate to the months selected.

If you want your linear trend line to IGNORE the months you selected, and to always calculate based on all months, you could use set analysis. I believe the expression would look like this:

linest_m({<Month=>} total aggr(if(sum({<Month=>}Sales),sum({<Month=>}Sales)),Month),Month)*Month
+linest_b({<Month=>} total aggr(if(sum({<Month=>}Sales),sum({<Month=>}Sales)),Month),Month)

Re: Forecasting in Qlikview

Thu, 14 Jun 2012 00:01:09 GMT

Dear John Not sure you have received my email dated May 27, 2012, kindly help me on this. Many Thanks & Best RdgsSteven From: steven-low@hotmail.com

To: qliktech@sgaur.hosted.jivesoftware.com

Subject: John, need urgent help from you

Date: Sun, 27 May 2012 09:20:26 +0800

Dear John

I know that you are an expert in QV and always help to resolve tough issue in the community.

Kindly advise how to compute Gross Profit and PBIT in the QV report.

Thanks

Steven

Re: Forecasting in Qlikview

Thu, 14 Jun 2012 08:30:20 GMT

Hi John,

Thanks for those new explanations that gave me new insights.

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.

Data:

LOAD

recno() as ID

,ceil(rand()*8) as Month

,ceil(rand()*10) as Sales

,ceil(rand()*3) as type

AUTOGENERATE 50

;

When I select a "type" the forecast is no more displayed in all the charts. I got only real datas even if I select the 3 items in my "type" field.

My goal would be to obtain forecast whatever selection is made in product or customer dimensions for instance. Is that possible ? Is that to be dealed with set analysis ?

Thanks

Regards,

Julien