16 Replies Latest reply: Dec 1, 2016 12:07 PM by Jeff Raguse RSS

    Is there a way to accumulate running totals across a dimension?

    Jeff Raguse

      I am fairly new to qlik sense, and have read several posts on similar topics, but have not found a direct answer to my situation.

       

      I have data with several dimensions, but want to accumulate running totals across a single dimension.  An example is the easiest way to explain what I am looking to do.

       

      My data has a month of origination dimension - VintMth (yyyymm) and an age dimension - MthsOrig (mm) along with other filtering dimensions and a measure that I wish to sum and total.  The chart shown below is after the first aggregation (the running sum).

       

       

      MthsOrig
      VintMth678910
      20130150006000650065007000
      2013026000600070008000
      201303800090009500
      Total190002100023000145007000

       

      I am looking for the "Total" row.  I have found that the following formula gives me the desired result for each "VintMth":

      sum(aggr(rangesum(above(total sum({<VINTMTH>} CHRGOFFAMT),0,RowNo()))

      ,VINTMTH,(MTHSORIG,numeric,ascending)))

       

      Essentially I believe this gives me a running sum for each VintMth (the rows shown above).  Now I wish to accumulate these running sum amounts down the "MthsOrig" columns (in order to give a "Total" for each MthsOrig), which looks like I would need another aggregation formula that I don't believe is allowed on top of an existing aggregation formula.

       

      Any ideas on how to solve this?