Skip to main content
Announcements
Global Transformation Awards! Applications are now open. Submit Entry
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Calculate Stock Levels

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....

ItemQuantityPurchased DateSales Date
Apples10001/01/201503/01/2015
Oranges5003/01/2015
Pears2502/01/2015
Apples20002/01/2015

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

DateStock Level
01/01/2015100
02/01/2015325
03/01/2015275
04/01/2015275

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

1 Reply
Peter_Cammaert
Partner - Champion III
Partner - Champion III

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

Oranges5003/01/2015
Pears2502/01/2015
Apples20002/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.