topic Re: Forecasting in QlikView by having only 6 months data in QlikView

Forecasting in QlikView by having only 6 months data

Vignesh1609 — Sat, 16 Nov 2024 20:15:09 GMT

Dear all,

I have an excel sheet which contains a sample data Shown below. I need to forecast for next 6 months sales with the current data i have. Kindly assist in Forecasting it.

I tried Cross table for Converting Month into rows and after that i have tried the forecasting methods from Forum. In which in Dimension it showing only the current data month not the Future months.

Customer name	Product Name	Product Type	Jan	Feb	Mar	Apr	May	June
Abc	Apple	Laptop	1,50,000		2,00,000	50,000		3,00,000
Hi5 logon	Dell	Desktop	75,000	1,00,000		2,50,000	30,000
XYZ	Dell	Laptop		3,00,000			50,000

Re: Forecasting in QlikView by having only 6 months data

Siva_Sankar — Tue, 20 Aug 2019 05:51:23 GMT

Hi Vignesh,

Refer this thread

https://community.qlik.com/t5/QlikView-Scripting/Forecast-Calculation/td-p/1211682

Re: Forecasting in QlikView by having only 6 months data

Siva_Sankar — Tue, 20 Aug 2019 05:58:54 GMT

Or my document on how to forecast here https://community.qlik.com/t5/QlikView-Documents/How-to-do-Forecasting-In-Qlikview-with-Example/ta-p/1491503

Re: Forecasting in QlikView by having only 6 months data

Josh_Berg_Support — Fri, 23 Aug 2019 15:14:44 GMT

Vignesh,

Did Siva's suggestion works for you? I took a look at the thread and the information appears to be what you're looking for. If your question has been answered, please mark to post as solved.

Thanks,
Josh

Qlik Support

Re: Forecasting in QlikView by having only 6 months data

Vignesh1609 — Sat, 31 Aug 2019 00:13:14 GMT

Dear Shiva,

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 in Advance

Vignesh Kumar