Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Vignesh1609
New Contributor II

Forecasting in QlikView by having only 6 months data

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 nameProduct NameProduct TypeJanFebMarAprMayJune
AbcAppleLaptop1,50,000 2,00,00050,000 3,00,000
Hi5 logonDellDesktop75,0001,00,000 2,50,00030,000 
XYZDellLaptop 3,00,000  50,000 
4 Replies
Siva_Sankar
Honored Contributor

Re: Forecasting in QlikView by having only 6 months data

Siva_Sankar
Honored Contributor

Re: Forecasting in QlikView by having only 6 months data

Re: Forecasting in QlikView by having only 6 months data

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

To help users find verified answers, please don't forget to use the "Accept as Solution" button on any posts that helped you resolve your problem or question.
Vignesh1609
New Contributor II

Re: Forecasting in QlikView by having only 6 months data

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;

clipboard_image_0.png

Thanks in Advance

Vignesh Kumar