Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

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

Exposure.PNG

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
swuehl
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:

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

2 Replies
swuehl
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:

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

philippon
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