Ageing Stock Analysis in Qlik Sense

    I was recently set the task to create a table that looked at Ageing Stock for one of my clients. It required looking at the date, Qty and Location of stock ‘In and outs’ on and First in First Out (FIFO) basis, and then producing values for date period buckets.

    The finished tables looked like this

     

    QS Table.PNG

     

    The first challenge was to work out the logic of the expressions, for this I used Excel. I created a dummy table with fake data to work out the equation for each bucket. The excel table looked like this:

     

    Excel Table.PNG

     

    I first worked out what number should be in each of the period balance cells, then set out to work out the expression for each starting at the 5th period backwards. The logic I used was;

     

    IF(G2-SUM(C3:G3)<=0,0,G2-SUM(C3:G3))


    IF the Purchase amount for period 5  (G2)

    is less than the sum of all sales (SUM(C3:G3)) Then 0

    ELSE Purchase amount (G2) minus the sum of all sales (SUM(C3:G3))

     

    The expressions for Period 1- 4 were different to 5 but similar to each other. Period 1 calculation shown below.

     

    IF(SUM(D2:G2)-SUM($C3:$G3) <=0,

    IF(SUM(C2:$G2)-SUM($C3:$G3)<=0,0,

    IF(SUM(C2:$G2)-SUM($C3:$G3) < C2,

    (SUM(C2:$G2)-SUM($C3:$G3)),C2 )),C2)

     

    IF the sum of previous purchases (SUM(D2:G2)) minus the sum of all sales (SUM($C3:$G3)) is less than or equal 0

    Then IF  the sum of all purchases (SUM(C2:$G2)) minus the sum of all sales (SUM($C3:$G3)) is less than or equal to 0 then 0

    Else IF the sum of all purchases (SUM(C2:$G2))  minus the sum of all sales (SUM($C3:$G3)) is less than the current period Purchases (C2)

    Then the sum of all purchases (SUM(C2:$G2))  minus the sum of all sales (SUM($C3:$G3))

    Else sum of current Purchases (C2)

     

    Once I had double checked the equations and checked they worked with all different permutations I then went about creating them in Qlik


    In the final table the Periods were

    • 0 - 92 Days
    • 93 – 184 Days
    • 185 – 276 Days
    • 276 – 365 Days
    • 365+ Days

    Here are the steps I followed to replicate in Qlik Sense:1) Create Variables for each of the periods as follows

      1. vDate0 : =Num(Today())
      2. vDate92 : =Num(Today()-92)
      3. vDate184 : =Num(Today()-184)
      4. vDate276 : =Num(Today()-276)
      5. vDate365+ : =Num(Today()-365)

    2) Create a Master Item Expression for each date periodThe Master Item Expression for 365+ Days corresponded with period 5 in excel and looked like this








    IF(Sum({< [RECEIPT] = {'Out'} , [DATE] = {"<$(vDate365+)"} >} QTY)

    -

    Sum({< [RECEIPT]  = {'In'} >} QTY) <= 0,

    0,

    IF(Sum({< [RECEIPT] = {'Out'} , [DATE] = {"<$(vDate365+)"} >} QTY)

    -

    Sum({< [RECEIPT]  = {'In'} >} QTY))

     

     

    The expressions for the other time periods were as follows (below for 0 – 92):

     

    IF(

      Sum({<[RECEIPT] = {'In'} , [DATE] = {"<$(vDate92)"} >} QTY)

      +

      Sum({<[RECEIPT] = {'Out'} >} QTY)

      <=0

    ,

    IF(

      Sum({<[RECEIPT] = {'In'} , [DATE] = {"<=$(vDate0)"} >} QTY)

        +

      Sum({<[RECEIPT] = {'Out'} >} QTY)

        <= 0,0

    ,

    IF(

      Sum({<[RECEIPT] = {'In'} , [DATE] = {"<=$(vDate0)"} >} QTY)

        +

        Sum({<[RECEIPT] = {'Out'} >} QTY)

        <

        Sum({<[RECEIPT] = {'In'} , [DATE] = {"<=$(vDate0) >=$(vDate92)"} >} QTY),

        Sum({<[RECEIPT] = {'In'} , [DATE] = {"<=$(vDate0)"} >} QTY)

        +

        Sum({<[RECEIPT] = {'Out'} >} QTY),

        Sum({<[RECEIPT] = {'In'} , [DATE] = {"<=$(vDate0) >=$(vDate92)"} >} QTY)))

        ,

        Sum({<[RECEIPT] = {'Out'} ,  [DATE] = {"<=$(vDate0) >=$(vDate92)"} >} QTY))

     

    As you copy the expression to the next date period master items you just need to change the Variables to the next date period variable, for example.             

     

                    <=$(vDate0) becomes <$(vDate92)

                    <$(vDate92) becomes <$(vDate184)

                    <$(vDate184) becomes <$(vDate276)

                    <$(vDate276) becomes <$(vDate365+)


    3) To finish the project off, insert a Table object onto your sheet, drag in the Location field, then drag in each master items starting with 0 - 92 Days.

    QS Table.PNG

     

    If you have any questions or comments please feel free to get in touch with me:

     

    Jon Foote is a Senior Qlik Consultant with Acumen BI and can be contacted at:

    E-Mail: jonf@acumenbi.co.nz