Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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;
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!
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.