topic Re: Forecast Calculation in QlikView

Forecast Calculation

pacoli2013 — Mon, 07 Nov 2016 13:53:41 GMT

Hello,

Is there a standard procedure for calculating a forecast of sales by extrapolation (accrued revenues diviert by expired months, then multiplied by months left till yearend )

for example: sales till this month November is 2 millions, so every month = 200.000, Extrapolation ist then

November : 2,2 millions

December : 2,4 millions

If there is a Standard procedure, can I build it in the loadstatement of my Measure Sales????

Re: Forecast Calculation

MarcoWedel — Mon, 07 Nov 2016 22:28:04 GMT

can you post some different examples and your expected result (best as file)?

thanks

regards

Marco

Re: Forecast Calculation

johnw — Tue, 08 Nov 2016 00:40:26 GMT

The attached example has several ways of showing a forecast. These just use chart expressions. If you really need to calculate it in script instead, I could probably figure it out.

Re: Forecast Calculation

johnw — Tue, 08 Nov 2016 00:42:01 GMT

Though in your case, it might be something simple like sum(Sales)*count(distinct Month)/12 or something like that. All depends on your data and what you really want to do, I think.

Re: Forecast Calculation

pacoli2013 — Tue, 08 Nov 2016 14:48:35 GMT

Hello John, thank you for your answer and Input .

I build it in in my InvoiceSalesmodel. then I discussed it with my Manager and some Users.

As always they came with new ideas:

The Forcast should predict next 6 months in advance,

Input amouts should be the last twelve months, for instance we are now in November so the Input should be from Last year November untill now and the Forecast should go from now (November 2016 ) till May next year.

I told them that I wished they figured that out earler. I hope you have some ideas?

Thanks in advance

Re: Forecast Calculation

johnw — Tue, 08 Nov 2016 17:58:55 GMT

It's been my experience that I rarely get the real requirements until I've already put a QlikView application in front of them. Until they can start interacting with the data, they either don't know or aren't willing to take the time to figure out what they really need in terms of charts and the like. Some of this is my fault, as it was that way for so long that I now by habit just get the most basic of "we want this data" requirements, and then slam out a very basic QlikView with how I might personally want to interact with that data, then solicit feedback. I usually get at least the data model right the first time (I'm usually very familiar with the data), and it's mostly a matter of building additional charts and ways of interacting with the data from there.

Anyway, that's all an aside. See the attached for a data model solution to your problem, since it seemed that was your preference. I'm sure it could be solved in the chart too if preferred. Here's the script of interest:

CONCATENATE (Data)
LOAD
monthstart(today(),iterno()-1) as Month
,monthstart(today(),iterno()-1)*Slope+Intercept as Sales
,'Forecast' as Type
WHILE iterno() <= 6
;
LOAD
linest_m(Sales,Month) as Slope
,linest_b(Sales,Month) as Intercept
;
LOAD
Month
,sum(Sales) as Sales
RESIDENT Data
WHERE Month >= monthstart(today(),-12)
GROUP BY Month
;

We're doing a linear regression over the past 12 months of data to predict the future six months of data. We also have types of Actual vs. Forecast, and these are associated with colors loaded in the script and used in the background color expression.

The linear trendline in the chart is only to show you that it works, and is based only on the previous 12 months. The trendline doesn't match the forecast when all months are shown, but does match when only the past 12 months and the forecast 6 months are shown.

Re: Forecast Calculation

pacoli2013 — Fri, 11 Nov 2016 10:04:51 GMT

Hello John,

Thanks for your support.

Your solution works, had to change some minor Details (like some fieldnames).

I hope my Manager likes what he get to see on Monday

Have a nice weekend

Court

Re: Forecast Calculation

biplabjana77 — Fri, 06 Jan 2017 12:51:55 GMT

Hi John,

Need Your help.

your solution is perfectly working for Organisation forecasting for future months.

But i have some more specific requirement

Let i have Monthwise, Branchwise,Product wise granular data of last 24 months in the following format :

Branch Product Month SaleAmount

A P01 Jan2016 1000

A P02 Jan2016 500

A P03 Jan2016 800

B P01 Jan2016 1000

B P02 Jan2016 500

B P03 Jan2016 800

And i have to forecast "Product wise","Branch wise", "SaleAmount" for futuremonths, i.e. say for month Feb,2017 and March'2017.

Can you please help...

Re: Forecast Calculation

johnw — Fri, 06 Jan 2017 23:51:05 GMT

Just add the dimensions you want to forecast by to the loads where you concatenate the forecast. See attached and script below. Well, this is forecasting 6 months from the past 12, but replace 6 and 12 with whatever you want in this script, and I'd think it would work like you want.

CONCATENATE (Data)
LOAD
Branch
,Product
,monthstart(today(),iterno()-1) as Month
,monthstart(today(),iterno()-1)*Slope+Intercept as Sales
,'Forecast' as Type
WHILE iterno() <= 6
;
LOAD
Branch
,Product
,linest_m(Sales,Month) as Slope
,linest_b(Sales,Month) as Intercept
GROUP BY
Branch
,Product
;
LOAD
Branch
,Product
,Month
,sum(Sales) as Sales
RESIDENT Data
WHERE Month >= monthstart(today(),-12)
GROUP BY
Branch
,Product
,Month
;

Re: Forecast Calculation

biplabjana77 — Mon, 09 Jan 2017 18:50:12 GMT

Hii John,,

Thanks for your support.Linear Trend works perfectly with my data

Can you please guide to develop the seasonal trend on the basis of the above generated Linear Trend.

i.e. for example, seasonal trend of Feb'2017 will be

Linear Trend of Feb'2017 + (Actual of Feb'2016 - Linear Trend of Feb'2016)

and this is require to generate at Branch,Product level.

Thanks in advance..

Re: Forecast Calculation

johnw — Tue, 10 Jan 2017 00:49:22 GMT

If I've understood, see below and attached. I'm now forecasting the next 12 months instead of next 6, because otherwise we can't visually see the relationship of the seasonal changes to the 12 month trend. You can use any number of months you want, it just makes it hard to visually verify that you have the right results. For a single Branch and Product and 12 months forecasted, you should see each forecasted month be the same distance from the trend line as the same month in the previous year. Well, except that I've assumed we can't have negative sales, so we don't go below 0, even if that's what the seasonal trend suggests.

Temp:
NOCONCATENATE
LOAD
Branch
,Product
,Month
,sum(Sales) as Sales
RESIDENT Data
WHERE Month >= monthstart(today(),-12)
GROUP BY
Branch
,Product
,Month
;
LEFT JOIN (Temp)
LOAD
Branch
,Product
,linest_m(Sales,Month) as Slope
RESIDENT Temp
GROUP BY
Branch
,Product
;
CONCATENATE (Data)
LOAD
Branch
,Product
,addmonths(Month,12) as Month
,rangemax(0,Sales+(addmonths(Month,12)-Month)*Slope) as Sales
,'Forecast' as Type
RESIDENT Temp
;
DROP TABLE Temp;

Re: Forecast Calculation

biplabjana77 — Wed, 11 Jan 2017 13:02:10 GMT

Hii John,,

One more time, thank you for your reply and help.

Regards
BJ

Re: Forecast Calculation

carlos13 — Fri, 02 Jun 2017 17:59:16 GMT

How can I calculate and apply a linear regression to see the balance of future dates?

From the data shown in the Excel of the link, I must calculate with the methodology of linear regression of future dates, the real is from 28/02/2017 backwards and future dates 01/03/2017 forward and the amounts They are zero, I need to Replace it with the projection, if you can help me I thank you.

What I would like to do is select a date from 2018 and be able to see the value of the projection. Is this possible in the load?

Regards,

https://community.qlik.com/thread/262596

Re: Forecast Calculation

BeeGees — Wed, 31 Jan 2018 06:16:15 GMT

I know its an old one, but could You please explain why for seasonal forecast calculation You use only slope (linest_m) and not intercept (linest_b)?

Re: Forecast Calculation

inbarbiri — Sun, 21 Apr 2019 08:05:27 GMT

Hi!
How can i use this code, but separate by category?
why does this doesn't work? (I added category ID from fact table)

LOAD
monthstart(Date(today()),iterno()-1) as SaleMonth,
monthstart(Date(today()),iterno()-1)*Slope+Intercept as Sales,
'Forecast' as sale_type
WHILE iterno() <= 6;
LOAD
linest_m(Sales,SaleMonth) as Slope,
linest_b(Sales,SaleMonth) as Intercept;
LOAD
SaleMonth,
CategoryID,
sum(Sales) as Sales
Resident Fact_Sales
WHERE SaleMonth >= monthstart(Date(today()),-6) and CANCEL_FLAG <> 'Y'
GROUP BY SaleMonth,CategoryID;

Re: Forecast Calculation

areebairfan — Tue, 14 May 2019 05:34:28 GMT

I have two Years of Data 2017 and 2018 for each 12 month. i want to predict next year sales in each month. can u help me how to forecast next year sales in each month.

Re: Forecast Calculation

Vignesh1609 — Sat, 31 Aug 2019 00:17:43 GMT

Dear John

Thanks for your Suggestion. I have tried following script but still i don't know where am missing.

I do have attached the screen shot of chart and also script i have tried.

Am getting values for only the data for actual month not for the forecasting month.

Kindly Help me out with this.

Script:

DataLoad:
LOAD [Party Name],
Product,
[Item Name],
Jan,
Feb,
Mar,
Apr,
May,
Jun,
Jul,
Aug,
Sep,
Oct,
Nov,
Dec
FROM
[D:\File\Sales - CHENNAI.xlsx]
(ooxml, embedded labels, table is [CHN - July]);

Transform:
CrossTable(Month,Sales,3)
Load *
Resident DataLoad;

FinalData:
NoConcatenate
LOAD [Party Name],
Product,
[Item Name],
Date#(Month,'MMM') As Month,
'2019' As Year,
Sales
Resident Transform
;

Data:
Load
[Party Name],
Product,
[Item Name],
Month,
Month&' '&Year As Month_Year,
// Month,
Num_Month,Type,
Sales
;
LOAD
[Party Name],
Product,
[Item Name],
Month,
Date#(Year,'YYYY') As Year,
NUM(Month(Month)) As Num_Month,
'Actual' As Type,
Sales
RESIDENT FinalData;

CONCATENATE (Data)
LOAD
[Party Name],
Product,
[Item Name],
// Month
monthstart(today(),iterno()-1) as Month_Year
,monthstart(today(),iterno()-1)*Slope+Intercept as Sales
,'Forecast' as Type
WHILE iterno() <= 6
;
LOAD
[Party Name],
Product,
[Item Name]
,linest_m(Sales,Month_Year) as Slope
,linest_b(Sales,Month_Year) as Intercept
GROUP BY
[Party Name],
Product,
[Item Name]
;
LOAD
[Party Name],
Product,
[Item Name],
Month_Year,
// Num_Month,
Sum(Sales) As Sales
RESIDENT Data
WHERE Month_Year >= monthstart(today(),-12)
GROUP BY
[Party Name],
Product,
[Item Name],
Month_Year
//Num_Month
;

Types:
LOAD *
,rgb(R,G,B) as Color
INLINE [
Type,R,G,B
Actual,100,150,200
Forecast,200,100,100
];

Drop Table FinalData;
Drop Table Transform;
Drop Table DataLoad;

EXIT SCRIPT;

Thanks for your Suggestion. I have tried following script but still i don't know where am missing.

I do have attached the screen shot of chart and also script i have tried.

Am getting values for only the data for actual month not for the forecasting month.

Kindly Help me out with this.

Script:

DataLoad:
LOAD [Party Name],
Product,
[Item Name],
Jan,
Feb,
Mar,
Apr,
May,
Jun,
Jul,
Aug,
Sep,
Oct,
Nov,
Dec
FROM
[D:\File\Sales - CHENNAI.xlsx]
(ooxml, embedded labels, table is [CHN - July]);

Transform:
CrossTable(Month,Sales,3)
Load *
Resident DataLoad;

FinalData:
NoConcatenate
LOAD [Party Name],
Product,
[Item Name],
Date#(Month,'MMM') As Month,
'2019' As Year,
Sales
Resident Transform
;

Types:
LOAD *
,rgb(R,G,B) as Color
INLINE [
Type,R,G,B
Actual,100,150,200
Forecast,200,100,100
];

Drop Table FinalData;
Drop Table Transform;
Drop Table DataLoad;

EXIT SCRIPT;

Thanks in Advance

Vignesh Kumar