Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Best Practice for Inventory / Stock Level calculation

Hi,

I am trying to find the best practice for presenting Inventory / Stock levels by day based on underlying transaction data that basically shows product, date, quanitity, where quantity can be positive (stock coming in) or negative (stock going out).

So far I have come up with three different solutions:

1. Some kind of IF-statement combined with a Date Island, e.g. =IF(TransDate<=Max(DateIslandDate))

2. Looping balances in script e.g.

FOR vDate = vStartDate to vEndDate

LOAD

vDate as BalanceDate,

ProdId,

Sum(Quantity) as Balance

RESIDENT TransTable

WHERE TransDate<=vDate

NEXT

3. Peek-solution similar to what HIC proposes:

Generating Missing Data In QlikView

With 10 000+ products all these solutions (except 1) take quite a long time to run and of course create a lot of rows if run over a couple of years 10 000 * 365 = 3.65 million per year. Alternative 3 gets quite complex if run over more dimensions than just Prod and Date. Alternative 2 is clean, but probably the slowest to run.

Anyone else has come up with better solutions?

Kind regards

Niklas

0 Replies