topic Re: Forecast from demand in QlikView

Forecast from demand

carg1 — Wed, 25 Nov 2020 16:16:04 GMT

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:

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:

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

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

Re: Forecast from demand

chrismarlow — Wed, 24 Jul 2019 19:11:34 GMT

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.

Re: Forecast from demand

carg1 — Thu, 25 Jul 2019 05:48:21 GMT

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:

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:

Thanks, Cargi.

Re: Forecast from demand

carg1 — Thu, 25 Jul 2019 11:31:28 GMT

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;

Thank for Your help Chris