topic Calculating Age of Inventory in QlikView

Calculating Age of Inventory

Anonymous — Mon, 03 Dec 2012 14:17:14 GMT

Hello

I have a set of historical inventory and I am trying to calculate the age of inventory for each product resetting to 0 each time the
inventory reaches 0.

Date, Product, Inventory are already in a table Exposure I loaded, and I am trying to add the Age column to it.

From my research, it seems like I should Iterate with a condition that the previous Inventory for each Product is not null, but I am lost in the process.

Thanks and regards

Re: Calculating Age of Inventory

swuehl — Mon, 03 Dec 2012 15:45:16 GMT

You just need to sort your table by Product and Date and then use peek() function, e.g. along these lines

Set DateFormat = 'DD/MM/YYYY';

INPUT:

LOAD * INLINE [

Date, Product, Inventory

03/12/2012,A,0

03/12/2012,B,10

03/12/2012,C,2

04/12/2012,A,0

04/12/2012,B,10

04/12/2012,C,2

05/12/2012,A,1

05/12/2012,B,5

05/12/2012,C,0

06/12/2012,A,2

06/12/2012,B,5

06/12/2012,C,1

07/12/2012,A,2

07/12/2012,B,5

07/12/2012,C,1

];

RESULT:

LOAD Date, Product, Inventory,

if(peek(Product) = Product and Inventory>0, rangesum(peek(Age),1),if(Inventory>0,1,0)) as Age

Resident INPUT order by Product, Date;

drop table INPUT;

Hope this helps,

Stefan

P.S.

Posting some sample data is great! But it would be even better if you've posted it as text, so we are able to copy paste it, not as bitmap image (or even better formatted as INLINE LOAD or in sample qvw).

Re: Calculating Age of Inventory

Anonymous — Mon, 03 Dec 2012 19:34:56 GMT

Many thanks.

It works perfect.

Sorry for the image format of the table, I tried to copy and paste but it looked awful.

Best