Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
Hi Vignesh,
Refer this thread
https://community.qlik.com/t5/QlikView-Scripting/Forecast-Calculation/td-p/1211682
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...
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
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