# QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Not applicable

## Calculating Age of Inventory

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

Tags (2)
1 Solution

Accepted Solutions
Not applicable

## Re: Calculating Age of Inventory

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:

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:

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

2 Replies
Not applicable

## Re: Calculating Age of Inventory

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:

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:

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

Not applicable

## Re: Calculating Age of Inventory

Many thanks.

It works perfect.

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

Best

Community Browser