20 Replies Latest reply: Feb 13, 2018 7:10 AM by Sunny Talwar RSS

    Inventory Ageing For Multiple Materials

    satish rathod

      Hello QlikExperts,

           I wanted to find the "Inventory Ageing" in Buckets for Different Materials ranging from 0-30 Days, 31-60 Days, 61-90 Days, 91-120 Days, 121-150 Days, 151-180 Days, 181-270 Days, 271-365 Days, 366-730 Days, and >730 Days. The Quantities in the Ageing  should be in FIFO (First In First Out) basis.

           'Debit' is "Ordered In" and 'Credit' is "Sold Out"

           So Debit will always be Greater than or Equal to Credit.

           The Date Format is in 'MM/DD/YY'.

           I am attaching a part of the data as a screenshot below.

      Capture_1.JPG

      The Output shown must be as follows which is shown above.

      The Material Quantities are (Debit-Credit).

        • Re: Inventory Ageing For Multiple Materials
          Devarasu R

          Hi,

          you can try to create age - bucket like below

          Buckets

           

          Creating Aging Buckets within QlikView

          • Re: Inventory Ageing For Multiple Materials
            Devarasu R

            Hi

            yeah it should work, try like below

            Fact:
            load *,
              If( Aging <= 30, Dual('0-30',1),
              If( Aging >= 30 and Aging <=60 , Dual('31-60',2),
              If( Aging >= 61 and Aging<=90 , Dual('61-90',3),
              If( Aging >= 91 and Aging<=120 , Dual('91-120',4),
              If( Aging >= 121 and Aging<=150 , Dual('121-150',5),
              If( Aging >= 151 and Aging<=180 , Dual('151-180',6),
              If( Aging >= 181 and Aging<=270 , Dual('181-270',7),
              If( Aging >= 271 and Aging<=365 , Dual('271-365',8),
            If( Aging >= 366 and Aging <=730 , Dual('366-730',9), Dual('>730',10)))))))))) as Bucket;
            
            
            load *,
            Interval(Now()- [Document Date] ,'dd') as Aging;
            LOAD * INLINE [
                Document Date, Material, Debit/Credit Status, Quantity
                1/1/2015, A, Debit, 50
                3/1/2015, B, Debit, 75
                3/1/2015, C, Debit, 25
                6/1/2015, B, Credit, 20
                8/1/2015, A, Debit, 20
                12/1/2015, A, Credit, 40
                2/1/2016, A, Debit, 30
                3/1/2016, C, Debit, 10
                7/1/2016, B, Debit, 50
                11/1/2016, B, Credit, 80
                5/1/2017, A, Debit, 10
                7/1/2017, C, Credit, 30
                8/1/2017, A, Credit, 20
                12/1/2017, A, Credit, 10
            ];
            
            

            Capture.JPG

            • Re: Inventory Ageing For Multiple Materials
              Sunny Talwar

              Try this may be

               

              Dimensions

              Material

              =Aggr(

              If(Today() - If((Sum(TOTAL <Material>{<[Debit/Credit Status] = {'Debit'}>}Quantity)-Sum(TOTAL <Material>{<[Debit/Credit Status] = {'Credit'}>}Quantity)) >=

              RangeSum(Above(Sum({<[Debit/Credit Status] = {'Debit'}>} Quantity)* Avg({<[Debit/Credit Status] = {'Debit'}>}1), 0, RowNo())), [Document Date],

              If((Sum(TOTAL <Material>{<[Debit/Credit Status] = {'Debit'}>}Quantity)-Sum(TOTAL <Material>{<[Debit/Credit Status] = {'Credit'}>}Quantity)) > (Alt(Above(RangeSum(Above(Sum({<[Debit/Credit Status] = {'Debit'}>} Quantity), 0, RowNo()))), 0)* Avg({<[Debit/Credit Status] = {'Debit'}>}1)),

              [Document Date]))

              * Avg({<[Debit/Credit Status] = {'Debit'}>}1) <= 30, Dual('0-30 Days', 1),


              If(Today() - If((Sum(TOTAL <Material>{<[Debit/Credit Status] = {'Debit'}>}Quantity)-Sum(TOTAL <Material>{<[Debit/Credit Status] = {'Credit'}>}Quantity)) >=

              RangeSum(Above(Sum({<[Debit/Credit Status] = {'Debit'}>} Quantity)* Avg({<[Debit/Credit Status] = {'Debit'}>}1), 0, RowNo())), [Document Date],

              If((Sum(TOTAL <Material>{<[Debit/Credit Status] = {'Debit'}>}Quantity)-Sum(TOTAL <Material>{<[Debit/Credit Status] = {'Credit'}>}Quantity)) > (Alt(Above(RangeSum(Above(Sum({<[Debit/Credit Status] = {'Debit'}>} Quantity), 0, RowNo()))), 0)* Avg({<[Debit/Credit Status] = {'Debit'}>}1)),

              [Document Date]))

              * Avg({<[Debit/Credit Status] = {'Debit'}>}1) <= 60, Dual('31-60 Days', 2),


              If(Today() - If((Sum(TOTAL <Material>{<[Debit/Credit Status] = {'Debit'}>}Quantity)-Sum(TOTAL <Material>{<[Debit/Credit Status] = {'Credit'}>}Quantity)) >=

              RangeSum(Above(Sum({<[Debit/Credit Status] = {'Debit'}>} Quantity)* Avg({<[Debit/Credit Status] = {'Debit'}>}1), 0, RowNo())), [Document Date],

              If((Sum(TOTAL <Material>{<[Debit/Credit Status] = {'Debit'}>}Quantity)-Sum(TOTAL <Material>{<[Debit/Credit Status] = {'Credit'}>}Quantity)) > (Alt(Above(RangeSum(Above(Sum({<[Debit/Credit Status] = {'Debit'}>} Quantity), 0, RowNo()))), 0)* Avg({<[Debit/Credit Status] = {'Debit'}>}1)),

              [Document Date]))

              * Avg({<[Debit/Credit Status] = {'Debit'}>}1) <= 90, Dual('61-90 Days', 3),


              If(Today() - If((Sum(TOTAL <Material>{<[Debit/Credit Status] = {'Debit'}>}Quantity)-Sum(TOTAL <Material>{<[Debit/Credit Status] = {'Credit'}>}Quantity)) >=

              RangeSum(Above(Sum({<[Debit/Credit Status] = {'Debit'}>} Quantity)* Avg({<[Debit/Credit Status] = {'Debit'}>}1), 0, RowNo())), [Document Date],

              If((Sum(TOTAL <Material>{<[Debit/Credit Status] = {'Debit'}>}Quantity)-Sum(TOTAL <Material>{<[Debit/Credit Status] = {'Credit'}>}Quantity)) > (Alt(Above(RangeSum(Above(Sum({<[Debit/Credit Status] = {'Debit'}>} Quantity), 0, RowNo()))), 0)* Avg({<[Debit/Credit Status] = {'Debit'}>}1)),

              [Document Date]))

              * Avg({<[Debit/Credit Status] = {'Debit'}>}1) <= 120, Dual('91-120 Days', 4),


              If(Today() - If((Sum(TOTAL <Material>{<[Debit/Credit Status] = {'Debit'}>}Quantity)-Sum(TOTAL <Material>{<[Debit/Credit Status] = {'Credit'}>}Quantity)) >=

              RangeSum(Above(Sum({<[Debit/Credit Status] = {'Debit'}>} Quantity)* Avg({<[Debit/Credit Status] = {'Debit'}>}1), 0, RowNo())), [Document Date],

              If((Sum(TOTAL <Material>{<[Debit/Credit Status] = {'Debit'}>}Quantity)-Sum(TOTAL <Material>{<[Debit/Credit Status] = {'Credit'}>}Quantity)) > (Alt(Above(RangeSum(Above(Sum({<[Debit/Credit Status] = {'Debit'}>} Quantity), 0, RowNo()))), 0)* Avg({<[Debit/Credit Status] = {'Debit'}>}1)),

              [Document Date]))

              * Avg({<[Debit/Credit Status] = {'Debit'}>}1) <= 150, Dual('121-150 Days', 5),


              If(Today() - If((Sum(TOTAL <Material>{<[Debit/Credit Status] = {'Debit'}>}Quantity)-Sum(TOTAL <Material>{<[Debit/Credit Status] = {'Credit'}>}Quantity)) >=

              RangeSum(Above(Sum({<[Debit/Credit Status] = {'Debit'}>} Quantity)* Avg({<[Debit/Credit Status] = {'Debit'}>}1), 0, RowNo())), [Document Date],

              If((Sum(TOTAL <Material>{<[Debit/Credit Status] = {'Debit'}>}Quantity)-Sum(TOTAL <Material>{<[Debit/Credit Status] = {'Credit'}>}Quantity)) > (Alt(Above(RangeSum(Above(Sum({<[Debit/Credit Status] = {'Debit'}>} Quantity), 0, RowNo()))), 0)* Avg({<[Debit/Credit Status] = {'Debit'}>}1)),

              [Document Date]))

              * Avg({<[Debit/Credit Status] = {'Debit'}>}1) <= 180, Dual('151-180 Days', 6),


              If(Today() - If((Sum(TOTAL <Material>{<[Debit/Credit Status] = {'Debit'}>}Quantity)-Sum(TOTAL <Material>{<[Debit/Credit Status] = {'Credit'}>}Quantity)) >=

              RangeSum(Above(Sum({<[Debit/Credit Status] = {'Debit'}>} Quantity)* Avg({<[Debit/Credit Status] = {'Debit'}>}1), 0, RowNo())), [Document Date],

              If((Sum(TOTAL <Material>{<[Debit/Credit Status] = {'Debit'}>}Quantity)-Sum(TOTAL <Material>{<[Debit/Credit Status] = {'Credit'}>}Quantity)) > (Alt(Above(RangeSum(Above(Sum({<[Debit/Credit Status] = {'Debit'}>} Quantity), 0, RowNo()))), 0)* Avg({<[Debit/Credit Status] = {'Debit'}>}1)),

              [Document Date]))

              * Avg({<[Debit/Credit Status] = {'Debit'}>}1) <= 270, Dual('181-270 Days', 7),


              If(Today() - If((Sum(TOTAL <Material>{<[Debit/Credit Status] = {'Debit'}>}Quantity)-Sum(TOTAL <Material>{<[Debit/Credit Status] = {'Credit'}>}Quantity)) >=

              RangeSum(Above(Sum({<[Debit/Credit Status] = {'Debit'}>} Quantity)* Avg({<[Debit/Credit Status] = {'Debit'}>}1), 0, RowNo())), [Document Date],

              If((Sum(TOTAL <Material>{<[Debit/Credit Status] = {'Debit'}>}Quantity)-Sum(TOTAL <Material>{<[Debit/Credit Status] = {'Credit'}>}Quantity)) > (Alt(Above(RangeSum(Above(Sum({<[Debit/Credit Status] = {'Debit'}>} Quantity), 0, RowNo()))), 0)* Avg({<[Debit/Credit Status] = {'Debit'}>}1)),

              [Document Date]))

              * Avg({<[Debit/Credit Status] = {'Debit'}>}1) <= 365, Dual('271-365 Days', 8),


              If(Today() - If((Sum(TOTAL <Material>{<[Debit/Credit Status] = {'Debit'}>}Quantity)-Sum(TOTAL <Material>{<[Debit/Credit Status] = {'Credit'}>}Quantity)) >=

              RangeSum(Above(Sum({<[Debit/Credit Status] = {'Debit'}>} Quantity)* Avg({<[Debit/Credit Status] = {'Debit'}>}1), 0, RowNo())), [Document Date],

              If((Sum(TOTAL <Material>{<[Debit/Credit Status] = {'Debit'}>}Quantity)-Sum(TOTAL <Material>{<[Debit/Credit Status] = {'Credit'}>}Quantity)) > (Alt(Above(RangeSum(Above(Sum({<[Debit/Credit Status] = {'Debit'}>} Quantity), 0, RowNo()))), 0)* Avg({<[Debit/Credit Status] = {'Debit'}>}1)),

              [Document Date]))

              * Avg({<[Debit/Credit Status] = {'Debit'}>}1) <= 730, Dual('366-730 Days', 9), Dual('>730 Days', 10))))))))))


              , Material, ([Document Date], (NUMERIC, DESC)))

               

              Expression

              =Sum(Aggr(If((Sum(TOTAL <Material>{<[Debit/Credit Status] = {'Debit'}>}Quantity)-Sum(TOTAL <Material>{<[Debit/Credit Status] = {'Credit'}>}Quantity)) >=

              RangeSum(Above(Sum({<[Debit/Credit Status] = {'Debit'}>} Quantity)* Avg({<[Debit/Credit Status] = {'Debit'}>}1), 0, RowNo())), Sum({<[Debit/Credit Status] = {'Debit'}>}Quantity),

              If((Sum(TOTAL <Material>{<[Debit/Credit Status] = {'Debit'}>}Quantity)-Sum(TOTAL <Material>{<[Debit/Credit Status] = {'Credit'}>}Quantity)) > (Alt(Above(RangeSum(Above(Sum({<[Debit/Credit Status] = {'Debit'}>} Quantity), 0, RowNo()))), 0)* Avg({<[Debit/Credit Status] = {'Debit'}>}1)),

              RangeMin(Sum({<[Debit/Credit Status] = {'Debit'}>}Quantity), (Sum(TOTAL <Material>{<[Debit/Credit Status] = {'Debit'}>}Quantity)-Sum(TOTAL <Material>{<[Debit/Credit Status] = {'Credit'}>}Quantity)))))

              * Avg({<[Debit/Credit Status] = {'Debit'}>}1), Material, ([Document Date], (NUMERIC, DESC))))

               

              Capture.PNG

                • Re: Inventory Ageing For Multiple Materials
                  satish rathod

                  Hi Sunny,

                  Thanks a lot man for taking time to help me with my problem.

                  However when I added some more Data, the Results are correct for Materials 'A' & 'C', But the Values are showing "+5" In Materials 'B' & 'D'

                  I am attaching the excel sheet as well the .qvf file

                  There can be an error from my end as well,

                  So it would be great if you'd let me know

                   

                  Thanks once again

                    • Re: Inventory Ageing For Multiple Materials
                      Sunny Talwar

                      Try this

                       

                      =Sum(Aggr(If((Sum(TOTAL <Material>{<[Debit/Credit Status] = {'Debit'}>}Quantity)-Sum(TOTAL <Material>{<[Debit/Credit Status] = {'Credit'}>}Quantity)) >=

                      RangeSum(Above(Sum({<[Debit/Credit Status] = {'Debit'}>} Quantity)* Avg({<[Debit/Credit Status] = {'Debit'}>}1), 0, RowNo())), Sum({<[Debit/Credit Status] = {'Debit'}>}Quantity),

                      If((Sum(TOTAL <Material>{<[Debit/Credit Status] = {'Debit'}>}Quantity)-Sum(TOTAL <Material>{<[Debit/Credit Status] = {'Credit'}>}Quantity)) > (Alt(Above(RangeSum(Above(Sum({<[Debit/Credit Status] = {'Debit'}>} Quantity), 0, RowNo()))), 0)* Avg({<[Debit/Credit Status] = {'Debit'}>}1)),

                      RangeMin(Sum({<[Debit/Credit Status] = {'Debit'}>}Quantity), (Sum(TOTAL <Material>{<[Debit/Credit Status] = {'Debit'}>}Quantity)-Sum(TOTAL <Material>{<[Debit/Credit Status] = {'Credit'}>}Quantity)-(Alt(Above(RangeSum(Above(Sum({<[Debit/Credit Status] = {'Debit'}>} Quantity), 0, RowNo()))), 0))))))

                      * Avg({<[Debit/Credit Status] = {'Debit'}>}1), Material, ([Document Date], (NUMERIC, DESC))))

                  • Re: Inventory Ageing For Multiple Materials
                    Sunny Talwar

                    Tried in QlikView and it seems to be working

                     

                    Table:

                    LOAD [Document Date],

                        Material,

                        [Debit/Credit Status],

                        Quantity

                    FROM

                    [..\..\..\Downloads\Inventory Ageing (1).xlsx]

                    (ooxml, embedded labels, table is Sheet1);


                    Left Join (Table)

                    LOAD Material,

                    Sum(If([Debit/Credit Status] = 'Debit', Quantity)) as DebitTotalQuantity,

                    Sum(If([Debit/Credit Status] = 'Credit', Quantity)) as CreditTotalQuantity

                    Resident Table

                    Group By Material;


                    Left Join (Table)

                    LOAD *,

                    If(Material = Previous(Material), Previous(CumQuantity), 0) as PreviousCumQuantity;

                    LOAD [Document Date],

                        Material,

                        [Debit/Credit Status],

                        If(Material = Previous(Material), RangeSum(Peek('CumQuantity'), Quantity), Quantity) as CumQuantity

                    Resident Table

                    Where [Debit/Credit Status] = 'Debit'

                    Order By Material, [Document Date] desc;


                    Left Join(Table)

                    LOAD *,

                    If(Today() - [Document Date] <= 30, Dual('0-30 Days', 1),

                    If(Today() - [Document Date] <= 60, Dual('31-60 Days', 2),

                    If(Today() - [Document Date] <= 90, Dual('61-90 Days', 3),

                    If(Today() - [Document Date] <= 120, Dual('91-120 Days', 4),

                    If(Today() - [Document Date] <= 150, Dual('121-150 Days', 5),

                    If(Today() - [Document Date] <= 180, Dual('151-180 Days', 6),

                    If(Today() - [Document Date] <= 270, Dual('181-270 Days', 7),

                    If(Today() - [Document Date] <= 365, Dual('271-365 Days', 8),

                    If(Today() - [Document Date] <= 730, Dual('366-730 Days', 9), Dual('>730 Days', 10)))))))))) as Bucket,

                    If(DebitTotalQuantity-CreditTotalQuantity >= CumQuantity, Quantity,

                    If(DebitTotalQuantity-CreditTotalQuantity > PreviousCumQuantity, RangeMin(Quantity, DebitTotalQuantity-CreditTotalQuantity-PreviousCumQuantity))) as New_Quantity

                    Resident Table

                    Where [Debit/Credit Status] = 'Debit'

                    Order By Material, [Document Date] desc;

                     

                    Pivot table

                     

                    Dimensions

                    Material

                    Bucket

                     

                    Expression

                    =Sum(New_Quantity)