Best Practice for Inventory / Stock Level calculation

Anonymous — Mon, 20 Oct 2014 08:48:23 GMT

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

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

topic Best Practice for Inventory / Stock Level calculation in QlikView

Best Practice for Inventory / Stock Level calculation