topic Calculate Stock Levels in QlikView

Calculate Stock Levels

Sun, 18 Jan 2015 19:51:52 GMT

Hi There QlikViewers

I am struggling to develop the expression I need to show my historic stock levels. The fields I am working with are;

Purchased Date
Sales Date

I want to be able to display my historic stock levels by referencing the Purchased Date to the Sales Date. So, in this example....

Item	Quantity	Purchased Date	Sales Date
Apples	100	01/01/2015	03/01/2015
Oranges	50	03/01/2015
Pears	25	02/01/2015
Apples	200	02/01/2015

...the stock levels by item quantity by date would be;

Date	Stock Level
01/01/2015	100
02/01/2015	325
03/01/2015	275
04/01/2015	275

I have been trying to work this out and have got completely stuck! Any help appreciated!

Re: Calculate Stock Levels

Peter_Cammaert — Sun, 18 Jan 2015 20:16:36 GMT

A suggestion:

In your script, create a stock movements table based on the rows you specified in the first list. The movements table has a single date column, a single movement quantity column and a single product column.

First store the stock additions in this table (only rows with Item, Quantity and Purchased Date), like:

Item Apples	Mvt Qty 100	Mvt Date 01/01/2015
Oranges	50	03/01/2015
Pears	25	02/01/2015
Apples	200	02/01/2015

Then add the stock subtractions (only rows with Item, Quantity and Sales Date). Negate the Quantity.

Item

Apples

Mvt Qty

-100

Mvt Date

03/01/2015

The order is unimportant. Showing stock levels on a certain date now becomes very simple.