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]

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.

[Anonymous]

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?

[johnw]

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.

[Anonymous]

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.

[johnw]

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.

[#7]

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

[#8]

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

[johnw]

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)

[#10]

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

[#11]

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