Calculating Age of Inventory

[Anonymous]

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

[swuehl · Accepted solution]

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).

[Anonymous]

Many thanks.

It works perfect.

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

Best