# New to QlikView

Discussion board where members can get started with QlikView.

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:

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:

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
];

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/

Tags (6)
1 Solution

Accepted Solutions
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:
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
];
data2:
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
Date,
Part,
[Quantity on stock]

Resident data;

Concatenate (Table)
Date,
Part,
Demand

Resident data2;
Drop Table data;
DROP Table data2;
//Final Table with calculated values
FinalTable:
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;``````

3 Replies
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:
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
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)
-Demand as Forecast,
*;
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.

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:

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.

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:
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
];
data2:
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
Date,
Part,
[Quantity on stock]

Resident data;

Concatenate (Table)
Date,
Part,
Demand

Resident data2;
Drop Table data;
DROP Table data2;
//Final Table with calculated values
FinalTable: