2 Replies Latest reply: Dec 3, 2012 2:34 PM by Pierre Philippon RSS

    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

        • Re: Calculating Age of Inventory
          Stefan Wühl

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