13 Replies Latest reply: Jan 24, 2018 6:55 AM by khaled ab RSS

    Inventory aging buckets

    khaled ab

      Dears,

      I'm working on inventory aging and i have been trying to distribute QTY & cost  based on receiving dates.

      the problem is :

       

      lets assume that i have 19 pieces of item A as on hand and on the other hand i have received this item on mulitpule dates & QTY (Total recievd 42 ,and 23 have been sold before the selected date  )

       

      the user will select 12/dec/2017 as of that date, i have 19 qty out of 42 received From item A , and this item has been received on the following dates

       

      Received dateQTY ReceivedSelected date -Recv DateBucketsOn Hand Qty
      28/Nov/20171330-601 of 19
      25/Nov/20171

      36

      0-601 of 19
      14/Jun/201710200180-36510 of 19
      13/Jun/201713201180-3657 of 19

       

       

      agign.PNG

      is it possible to be done in qlik.

        • Re: Inventory aging buckets
          Siddharth Sheshadri

          Can you articulate better as to what you are trying to achieve?

          I am unable to comprehend the problem here.

            • Re: Inventory aging buckets
              khaled ab

              let me try this again.

              I am working on inventory aging where i need to distribute stock items into buckets based on two dates

              first one will be selected by the user(31/DEC/2017) the second one based on the multi received dates.

               

              now for item A i have 19 as on hand and initially there are 42  which have been received (23 have been sold)

               

              now to calculate the aging bucket i need to go check the receiving dates and QTY received to distribute these 19 QTY.

               

              check the dates on the below Picture.

              Receiving Dates.PNG

               

              so my on hand QTY is 19 , these 19 should be distributed into bucket based on the receiving dates  as the below picture

               

              Capture.PNG

              i hope this would make sense to everyone

                • Re: Inventory aging buckets
                  Andy Weir

                  If you are doing in the front end then a calculated dimension in master items or the chart itself should handle the bucketing something like

                   

                  IF(Difference between days < 60, '0-60'

                  IF(Difference between days < 180, '61-179'

                  IF(Difference between days < 366, '180-365', 'N/A')))

                   

                  Regards

                   

                   

                  Andy

              • Re: Inventory aging buckets
                Sunny Talwar

                Would you be able to share some data in its raw form in an Excel file to see what you have....

                  • Re: Inventory aging buckets
                    khaled ab

                    allow me to further explain the case im having

                    i have merged three reports (total stock,booked,damage) in order to calculate the sound stock which is equal to

                    Total Stock-(Booked + Damage)

                    now what im trying to achieve is to distribute the sound stock (cost and Qty) into aging buckets.

                    these aging buckets is based on two dates

                    first= select date by the user

                    Second= Multiple receiving Dates

                    lets take the below image as example:

                    Kuka item.PNG

                     

                     

                    now the selected date is 12/31/2017, and the below image show the receiving dates for that item

                    Note:we should only consider receiving dates which are less or equal to the selected Date


                     

                    Receving Date.PNG

                     

                    now lets look at how the Sound Qty and Cost  Should be distributed

                    from the 1st image we can see that we have 99 as sound stock of that item, these 99 Qty should be distributed as below

                     

                    Qty Distribuation.PNG

                     

                     

                    the reason for doing this is to achieve the below

                    Aging buckets.PNG

                     

                    PFA

                      • Re: Inventory aging buckets
                        khaled ab

                        stevedark can you please have a look on the above

                          • Re: Inventory aging buckets
                            Steve Dark

                            Hi Khaled,

                             

                            As far as I can see there is not a 100% straight forward answer to this problem.

                             

                            Just to confirm; you want to find the current on hand stock for each object, and then work out the age of stock that you have on hand, based on the assumption that the stock is moved on a "First in First out" basis?

                             

                            I'm assuming that no user selections in the front end is going to impact on this in any way shape or form?

                             

                            As such, I would look at dealing at this in the load script, to make the front end calculations easier.


                            To do this, load the table from which you can get stock on hand for each product, then load this into a MAPPING TABLE (you may or may not need a group by for this).

                             

                            Map_ProdOnHand:

                            MAPPING LOAD

                              SKU,

                              OnHand

                            RESIDENT CurrentStock;

                             

                            Now map that onto the deliveries table.  You will need to parse this table twice, so that you can apply the map and apply the order by.  So, the first pass would be:

                             

                            tmpDeliveries:

                            LOAD
                               SKU,

                               DelDate,

                               Floor(DelDate - Today()) as DaysAgo,

                               ApplyMap('Map_ProdOnHand', SKU, 0) as TotalOnHand

                               etc.

                             

                            You can then load the rows in product / delivery order and check the cumulative stock, with a peek;

                             

                             

                            Deliveries:

                            LOAD
                              *,
                              if(IsCurrentStock, DeliveryAgedGroup, null()) as CurrentStockAgedGroup,

                              if(IsCurrentStock, Qty, null()) as CurrentStockDeliveredQty

                                 ;

                            LOAD
                               *,

                               if(PrevQty <= TotalOnHand, True, False) as IsCurrentStock

                               ;

                            LOAD

                               SKU,

                               DelDate,

                               DaysAgo,

                              if(DaysAgo<= 60, Dual('0-60', 0),

                                 if(DaysAgo <= 180, Dual('61-180', 61),

                                 etc..

                                 ))))))))  as DeliveryAgedGroup,

                               TotalOnHand,

                               Qty,

                               if(peek(SKU, -1) = SKU, CumQty, 0) as PrevQty,

                               Qty + if(peek(SKU, -1) = SKU, CumQty, 0) as CumQty,

                               etc.

                            RESIDENT tmpDeliveriers

                            ORDER BY SKU, DelDate DESC

                            ;

                             

                            DROP TABLE Deliveries;

                             

                            You may want to then drop some or all of the work in progress fields, such as TotalOnHand, PrevQty and CumQty as these will cause double counting if someone tries to tot them up in the front end.  They could be useful for debugging though.

                             

                            Once you have the CurrentStockAgedGroup and CurrentStockDeliveredQty fields created the build of the front end should be straight forward.


                            If you need to get it accurate regarding qty in current stock you may need to have some of one delivery in CurrentStockDeliveredQty and some not.  You can do this by comparing TotalOnHand and CumQty and taking off the difference from the delivery which takes you over the current on hand.


                            Hopefully I've understood what you are after and have explained the solution well enough.


                            All the code has been typed directly into Qlik Community - so there may well be some syntax issues, hopefully it points you in the right direction though.


                            Cheers,

                            Steve

                              • Re: Inventory aging buckets
                                khaled ab

                                Thanks Steve for your response and valuable info.

                                "Just to confirm; you want to find the current on hand stock for each object, and then work out the age of stock that you have on hand, based on the assumption that the stock is moved on a "First in First out" basis?"


                                Yes you right the stock is on the basis of "First In First Out"


                                "I'm assuming that no user selections in the front end is going to impact on this in any way shape or form?"


                                Dealing with it on the back end would be the optimal solution, but the requirements is to allow the user to select the date instead of Today() therefore this needs to be moved to the front end in order to calculate the buckets accordingly.


                                The problem is that one item can have different aging buckets depending on the Sound stock, receiving dates & Receiving QTY.

                                lets take the below example:

                                assuming the user has selected :31/12/2017

                                Item Code:

                                03-21464-MCO-00-71

                                Sound QTY:42

                                 

                                now these QTY should be distributed as below :

                                03-21464-MCO-00-71.PNG

                                 

                                the above is achieved using PL-SQL with the use of cursor and for loops after taking date as an input from the user.

                                the goal is to achieve the below

                                Aging buckets.PNG