Calculate Inventory with Inventory Movements

[Oliver1]

Hello everybody,

I have a table with the actual stock from only one specific day and a table with all movements before this day, so +/- inventory movements for each article. 

Now i want to calculate the stock level for each day and save it in a separate inventory table or add all the stock levels for each day to the allready existing table with the data only form this one day, is there a possibility to do this in the load script?

Thank you and br,

Oliver

[caiogil1]

Hi @Oliver1 ,

It will be easier to help you if you provide some data sample.

But see if this result in image (attachment) from a data raw sample will help you. It creates the position of Stock at each day since the start (min date from data source) to the last date (max date from data source), considering the movements (IN or OUT) of the Stock. Also I attached the .qvf to you take a look in the ETL used, I recommend you to do this at this stage to not make your Qlik App heavy with front end calculations.

Obs.: date format is 'DD/MM/YYYY'

[Oliver1]

Hi @caiogil1 ,

thank you very much, there is just one thing, but this is my fault, here are some example tables:

I have the inventory for all articles only for 25.05.2020:

location_id	item_id	inventory_date	quantity
001	A	25.05.2020	100
001	B	25.05.2020	90
001	C	25.05.2020	80
002	A	25.05.2020	50

And I have movements for everyday from 01.01.2019 until 25.05.2020:

location_id	item_id	inventory_date	direction	quantity
001	A	24.05.2020	out	20
001	A	24.05.2020	in	30
001	B	24.05.2020	out	50
002	A	24.05.2020	in	10

Now I want to add the inventory_quantity for each day in the first table.

So exactly what you have allready posted, just the first table is missing.

Thank you and br,

Oliver