Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

carg1
New Contributor III

Forecast from demand

Dear Qlikview experts,

I am trying to solve this, on first, easy task but I am really stuck on it for some time and I cannot find similar topic on internet.
I will try to explain my problem in short:

I have loaded data like for example: 

Load*Inline [
Date,Part,Quantity on stock
7/14/2019,A,20
7/14/2019,B,20
7/14/2019,C,20
7/14/2019,D,20
7/14/2019,E,20
7/14/2019,F,20
7/24/2019,A,25
7/24/2019,B,25
7/24/2019,C,25
7/24/2019,D,25
7/24/2019,E,25
7/24/2019,F,25
];

Now I know how many parts is there on stock  up until today, but now my task is to implement a "Forecast" by knowing Part Demands in future for example:

Load*Inline [
Date,Part,Demand
7/28/2019,A,5
7/28/2019,B,2
7/28/2019,C,14
7/28/2019,D,10
7/28/2019,E,2
7/28/2019,F,2
7/30/2019,A,5
7/30/2019,B,5
7/30/2019,C,8
7/30/2019,D,5
7/30/2019,E,11
7/30/2019,F,5
];

Task:

Table.PNG

What I did is fill the future with the last non zero value using Above then accumulate the demand and then subtract these two values. 
The problem is that I cannot plot this and also I need to have two dimensions on chart Date and Part. 

My final goal would be something like this but for all parts on one graph:


Graph.PNGSource: http://www.learnqlikview.com/category/qlikview-apps/page/6/

Chart source: http://www.learnqlikview.com/category/qlikview-apps/page/6/

1 Solution

Accepted Solutions
carg1
New Contributor III

Re: Forecast from demand

Dear members,

I manage to solve this task so I want to share the solution if anyone ever need it. 

//Loading data (from past)
data:
Load*Inline [
Date,Part,Quantity on stock
7/13/2019,A,10
7/14/2019,A,20
7/14/2019,B,5
7/14/2019,C,14
7/14/2019,D,18
7/14/2019,E,16
7/14/2019,F,18
7/24/2019,A,25
7/24/2019,B,18
7/24/2019,C,17
7/24/2019,D,13
7/24/2019,E,28
7/24/2019,F,11
];
//Loading demands in future
data2:
Load*Inline [
Date,Part,Demand
7/28/2019,A,5
7/28/2019,B,2
7/28/2019,C,14
7/28/2019,D,10
7/28/2019,E,2
7/28/2019,F,2
7/30/2019,A,5
7/30/2019,B,5
7/30/2019,C,8
7/30/2019,D,5
7/30/2019,E,11
7/30/2019,F,5
7/31/2019,A,3
];
//Makeing one table for calculations
Table:
NoConcatenate
LOAD 
	Date,
	Part,
	[Quantity on stock]
	
Resident data;

Concatenate (Table)
LOAD
	Date,
	Part,
	Demand
	
Resident data2;
Drop Table data;
DROP Table data2;
//Final Table with calculated values 
FinalTable:
Load 
	Date,
	Part,
	[Quantity on stock],
	Demand,
	If(IsNull([Quantity on stock]),Peek(Forecast,-1),[Quantity on stock]) as Forecast,
	If(IsNull(Demand),0, Peek(ACCDemand,-1)+ Demand) as ACCDemand

Resident Table order by Part;
DROP Table Table;

44.PNG

 

Thank for Your help Chris

View solution in original post

3 Replies
chrismarlow
Valued Contributor

Re: Forecast from demand

Hi,

In the script you could try combining into one table and calculating the forecast figures, something like;

data:
Load*Inline [
Date,Part,Quantity on stock
7/14/2019,A,20
7/14/2019,B,20
7/14/2019,C,20
7/14/2019,D,20
7/14/2019,E,20
7/14/2019,F,20
7/24/2019,A,25
7/24/2019,B,25
7/24/2019,C,25
7/24/2019,D,25
7/24/2019,E,25
7/24/2019,F,25
];

data2:
NoConcatenate
Load
	If(RowNo()=1,
		[Quantity on stock], 
		If(Peek(Part)<>Part,
			[Quantity on stock], 
			[Quantity on stock]-Peek([Quantity on stock])
			)
		) AS Forecast,
	*
Resident data
Order By Part, Date;

concatenate (data2)
Load
	-Demand as Forecast,
	*;
Load*Inline [
Date,Part,Demand
7/28/2019,A,5
7/28/2019,B,2
7/28/2019,C,14
7/28/2019,D,10
7/28/2019,E,2
7/28/2019,F,2
7/30/2019,A,5
7/30/2019,B,5
7/30/2019,C,8
7/30/2019,D,5
7/30/2019,E,11
7/30/2019,F,5
];

drop table data;

Cheers,

Chris.

carg1
New Contributor III

Re: Forecast from demand

Hi Chrismarlow, 

Thank You for Your response. I tried to use this script but it doesn't work as I was hoping for. I will try to explain my task in little more detail. 
Quantity on stock is a known quantity on our warehouse from past until today. Demand means how many parts we will need on particular date. 
Therefore my task is to plot quantity on stock up until today like here:

A.PNG

and then assume that on the next date (7/28/2019)  I will have Quantity on stock (7/24/2019) - Demand(7/28/2019). After, on the date (7/30/2019) = Previous Calculated quantity on (7/28/2019)  - Demand (7/30/2019)
or  the date (7/30/2019) = Quantity on (7/24/2019) - Demand(7/28/2019) - Demand (7/30/2019) etc.

So the result for part A  would be: 

7/14/2019,A,20
7/24/2019,A,25
7/28/2019,A,25 - 5[Demand on (7/28/2019)] = 20
7/30/2019,A,20 - 5[Demand on 7/30/2019] = 15

OR Also possible:

7/30/2019,A,25 - 5[Demand on 7/28/2019] -  5[Demand on 7/30/2019] = 15

 

Expected result for part A: 

B.PNG

Thanks, Cargi.

carg1
New Contributor III

Re: Forecast from demand

Dear members,

I manage to solve this task so I want to share the solution if anyone ever need it. 

//Loading data (from past)
data:
Load*Inline [
Date,Part,Quantity on stock
7/13/2019,A,10
7/14/2019,A,20
7/14/2019,B,5
7/14/2019,C,14
7/14/2019,D,18
7/14/2019,E,16
7/14/2019,F,18
7/24/2019,A,25
7/24/2019,B,18
7/24/2019,C,17
7/24/2019,D,13
7/24/2019,E,28
7/24/2019,F,11
];
//Loading demands in future
data2:
Load*Inline [
Date,Part,Demand
7/28/2019,A,5
7/28/2019,B,2
7/28/2019,C,14
7/28/2019,D,10
7/28/2019,E,2
7/28/2019,F,2
7/30/2019,A,5
7/30/2019,B,5
7/30/2019,C,8
7/30/2019,D,5
7/30/2019,E,11
7/30/2019,F,5
7/31/2019,A,3
];
//Makeing one table for calculations
Table:
NoConcatenate
LOAD 
	Date,
	Part,
	[Quantity on stock]
	
Resident data;

Concatenate (Table)
LOAD
	Date,
	Part,
	Demand
	
Resident data2;
Drop Table data;
DROP Table data2;
//Final Table with calculated values 
FinalTable:
Load 
	Date,
	Part,
	[Quantity on stock],
	Demand,
	If(IsNull([Quantity on stock]),Peek(Forecast,-1),[Quantity on stock]) as Forecast,
	If(IsNull(Demand),0, Peek(ACCDemand,-1)+ Demand) as ACCDemand

Resident Table order by Part;
DROP Table Table;

44.PNG

 

Thank for Your help Chris

View solution in original post