Insight in stocklevels

[tomgrouwe]

Hello experts,

I need to make a report in which we can see our current, and stock levels from the past. I did some research in our database en the best table to use is a table with stock mutations.

All stock is kept on a carrier. Carriers are unique values on which mutations are registered. In the example below you see two mutations on 1 carrier. The first mutation is the stock coming in, the second mutation is stock going out. In between it is possible that there is a correction en movement to a different storage location.

In the example i have two dates. A date in and a date out. In between i want to make dates. On each date it also needs to make the quantity (e.g. on 12-10-2018 quantity = 6, on 25-10-2018 quantity = 0), and count the carrier because it is in stock (after date out the carrier is also out of stock). Via this way i think it is possible to report stocklevels for each customer, per Warehouse, per article.

In the appendix you'll find the script.

Can someone help me with this?

[Gysbert_Wassenaar]

You could create reference dates: https://community.qlik.com/t5/Qlik-Design-Blog/Creating-Reference-Dates-for-Intervals/ba-p/1463944

Or  you could create an AsOf table: https://community.qlik.com/t5/Qlik-Design-Blog/The-As-Of-Table/ba-p/1466130

The first is probably easier to understand and work with. But if you have a large fact table it could increase the amount of data a lot. The As-Of table might perform better with a large fact table.