topic Inventory levels calculation in QlikView

Inventory levels calculation

Mon, 04 May 2009 18:43:31 GMT

We need some suggestions for solving this calculation.

We have stores that perform daily inventory levels of various products.

Some stores do not report their levels every day, and may skip several days.

How do I provide a table of the actual inventory level on any given day for every store, even if a store did not report on the day requested, it picks the last inventory reported.

Example: Store 1 reports on Jan 1, 2,5,6; Store 2 reports on Jan 1,3,6; store 3 reports on Jan 1, 4, 6

I pick date Jan 4 to see levels as of Jan 4, the table should show the inventory levels for store 1 from Jan 2 ; store 2 from Jan 3 and store 3 from Jan 4.

Any suggestions?

Inventory levels calculation

Anonymous — Mon, 04 May 2009 19:21:10 GMT

My recommendation is to fill missing days with the previous values. I've answered a similar question a while ago, and still have the example - see attached.

Inventory levels calculation

Mon, 04 May 2009 19:32:27 GMT

This works only if the data source contains rows for the unreported dates, my data does contain any rows if the store does not report.

Inventory levels calculation

Anonymous — Mon, 04 May 2009 19:36:14 GMT

That's expected. You have first to create a list of all dates (calandar), and join it with the existing data, and only after that fill the missing days.

Inventory levels calculation

Mon, 04 May 2009 19:44:05 GMT

Thanks let me give this a try.

Inventory levels calculation

Mon, 04 May 2009 20:32:59 GMT

Hi - your application is not filling the null rows with the previous value,can you check it?

Inventory levels calculation

Anonymous — Mon, 04 May 2009 21:52:11 GMT

I sent a wrong file, , my apology.
Try the attched

Inventory levels calculation

prieper — Mon, 04 May 2009 21:59:33 GMT

Hi Michael,

think script should also sort by Site and Date before filling the gaps?

/Peter

Inventory levels calculation

Anonymous — Mon, 04 May 2009 22:11:26 GMT

Sure it should be sorted properly. In this simple example it's sorted in the datasource itself, so I didn't bother.
BTW, I had a problem using this method when loading data from a database, although it worked fine from the file. As a workaround, I had to load data from the database into the qvd file, and fill the blanks while reading from qvd.

Inventory levels calculation

rwunderlich — Tue, 05 May 2009 00:21:45 GMT

There is a similar example in the QV Cookbook that includes generating the range of dates. See the sample titled "Fill values in a data range using previous values.".

Download the QV Cookbook from:
http://robwunderlich.com/Download.html

-Rob

Inventory levels calculation

suniljain — Thu, 09 Dec 2010 09:57:49 GMT

Dear Rob,

its good example. you applied good logic.

Thanking you

Sunil