5 Replies Latest reply: Nov 30, 2013 2:14 AM by Nilesh Gangurde RSS

    Stock Aging - As of Date logic

      Hi All,

       

      I just stuck in stock aging calculation and i need your view.

       

      I have a data like that.

       

      IDTYPEDateQty
      1Inventory01.01.2012100
      1Inventory05.02.201225
      1Sales07.02.201225
      1Inventory07.02.201220
      1Sales10.03.201225
      1Sales17.04.201225

       

      I try to create a stock aging report for "as of date" logic with FIFO. it should not be latest stock level's aging.

       

      For example; if the business user wants to see the stock aging report for 01.04.2012 it shoud be calculating depending on that date.

       

      In the examples that i find over the community does not care the sales date. it find the latest stock level and redistribute the stock according to that level.

       

      Does anyone has an idea about how to to do that?

       

      thanks.

       

      Omer

        • Re: Stock Aging - As of Date logic

          hi omer,

           

          i dont have the whole picture but im thinking of somthing to be done within the load statment...

           

          try doing an initial load and than use a resident load where you can sort your data by product id and date,

          next you will be able to calculate a comulative stock and stock age using the previouse() function.

           

          somthing like:

           

          Stock:

          LOAD *,

           

          Date - Previouse(Date) as StockAge;

           

           

           

          Resident StockTmp (in this table you initialy loaded your data)

           

          Order by `ID`, `Date`;

           

           

          Drop Table StockTmp;

           

           

          this way you can hold a relevant stock level and stock age for each date and that level can be later selected by the end user...

          • Re: Stock Aging - As of Date logic

            Hi,

             

            Thanks for the reply.

             

            Actually, we can not directly look at previous date.. if the previous date is also sales, then you may miss that point.

             

            I seperate positive entries and sales in my script and then redistribute the positive values according to final stock level..

             

             

            to find remaining stock, i use the below script.

             

             

            load *, if(%Key_OnHand = peek('%Key_OnHand'),

                                if(peek('Remaining_Stock')>0,peek('Remaining_Stock')-PRIMARY_QUANTITY),OnHandQty-PRIMARY_QUANTITY) as Remaining_Stock

             

             

            thanks.

             

            Omer

              • Re: Stock Aging - As of Date logic

                i would seperate it to several phases:

                 

                1. calculating stock level for stock entry points - for that i wouldnt have exclude the sales but multiplie them by (-1) to get the updated stock.

                 

                2. after we have the updated stock for all dates, in the resident query i would insert a 'where' in the resident to exclude the sales, only after we calculated the relevant stock level.

                 

                3. i would also use qualify to make sure this data doesnt mix with other data because its a summerized data.

                 

                this should be very simple.....

                  • Re: Stock Aging - As of Date logic

                    yes, phases that you are talking are very simple and straigth forward...

                     

                    but in this case, you miss one point; when you create a summary view according to + and -

                    to find the final stock level, you could not say this 100 stock comes from which stock entry.

                     

                    to do that you should create a loop for each item for each day.. since the data volume that i am dealing is so huge, i need to find some other way to handle this case.

                     

                    i attach a simple QV document for your reference to make it clear for you.

                     

                    In my sample data set, first 2 entries are + and they are 100, 25. after those entries there is a sales which is 25 and stock level drops to 100..

                     

                    if i do your suggestion; my final stock level will seen as 100 and my first stock level will seen 100 and it says, it is related with that.. but according to FIFO (First In First Out), my 25 sales should be substract from my first inventory entry which 100..

                     

                    I hope it will be much more clear for you now...