Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.

Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Qlik Community
- :
- All Forums
- :
- QlikView App Dev
- :
- Forecast from demand

Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

carg1

Contributor III

2019-07-24
04:03 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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:**

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:

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

1,059 Views

1 Solution

Accepted Solutions

carg1

Contributor III

2019-07-25
07:31 AM

Author

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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

1,001 Views

3 Replies

chrismarlow

Specialist II

2019-07-24
03:11 PM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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

Contributor III

2019-07-25
01:48 AM

Author

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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

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.

1,017 Views

carg1

Contributor III

2019-07-25
07:31 AM

Author

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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

1,002 Views

Community Browser