3 Replies Latest reply: Feb 21, 2012 1:15 AM by Sajeevan Govindan RSS

    Help with rolling balance in inventory ageing analysis

      Hi,

       

      I am trying to make an inventory ageing analysis with a rolling balance based on some defined time buckets.

      The raw data is inventory transaction details. I have been able to make the report in a vertical pivot format, but struggle to make it in a horizontal pivot format.

       

      I have attached a sample qlikview app with data and an excel sheet with the logic I am trying to acheive.

       

      All helps and hints appreciated.

       

      Regards,

      Freddy

        • Re: Help with rolling balance in inventory ageing analysis
          Sajeevan Govindan

          Hi Freddy,

           

          You can use the below expression to get the desired result.

           

          if(

          rangesum(

          if(isnull(aggr(nodistinct Sum({<TransactionType={'5'}>} QtyTrans), Item)),

          Before(aggr(nodistinct Sum({<TransactionType={'5'}>} QtyTrans), Item),1,1),

          aggr(nodistinct Sum({<TransactionType={'5'}>} QtyTrans), Item))

          ,

          Before(Sum({<TransactionType={'1','3'}>}QtyTrans),0,1),

          Before(Sum({<TransactionType={'1','3'}>}QtyTrans),1,1),

          Before(Sum({<TransactionType={'1','3'}>}QtyTrans),2,1),

          Before(Sum({<TransactionType={'1','3'}>}QtyTrans),3,1)

          )

          < 0,0,

          rangesum(

          if(isnull(aggr(nodistinct Sum({<TransactionType={'5'}>} QtyTrans), Item)),

          Before(aggr(nodistinct Sum({<TransactionType={'5'}>} QtyTrans), Item),1,1),

          aggr(nodistinct Sum({<TransactionType={'5'}>} QtyTrans), Item))

          ,

          Before(Sum({<TransactionType={'1','3'}>}QtyTrans),0,1),

          Before(Sum({<TransactionType={'1','3'}>}QtyTrans),1,1),

          Before(Sum({<TransactionType={'1','3'}>}QtyTrans),2,1),

          Before(Sum({<TransactionType={'1','3'}>}QtyTrans),3,1)

          )

          )

           

          Attaching the modified QVW file too.

           

          Regards,

          Sajeevan