# Qlik Sense App Development

Not applicable

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

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 VintMth 6 7 8 9 10 201301 5000 6000 6500 6500 7000 201302 6000 6000 7000 8000 201303 8000 9000 9500 Total 19000 21000 23000 14500 7000

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?

Tags (2)
16 Replies
MVP

## Re: Is there a way to accumulate running totals across a dimension?

So everything else looks good and you are only aiming to get the total row? Is that what you are trying to do?

Not applicable

## Re: Is there a way to accumulate running totals across a dimension?

Correct.

The formula above does give the correct running sum of the individual amounts, but what I really need is the total row.

MVP

## Re: Is there a way to accumulate running totals across a dimension?

So this expression isn't giving you the right numbers?

Sum(Aggr(Rangesum(Above(TOTAL Sum({<VINTMTH>} CHRGOFFAMT), 0, RowNo()))

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

Not applicable

## Re: Is there a way to accumulate running totals across a dimension?

Not for the "Total" row.

That expression does give the correct numbers for each VintMth separately, but I really need the "Total" row - which would be the accumulation of all running sum subtotals across the VintMth dimension for each MthOrig (e.g. 23000 = 6500+7000+9500).

MVP

## Re: Is there a way to accumulate running totals across a dimension?

What do you see right now? Would you be able to share a sample to check this out?

Not applicable

## Re: Is there a way to accumulate running totals across a dimension?

I am going to attach an app with sample data.  The expression above has been entered and gives a running sum for each VintMth as is shown in the app line chart and the table.

I now think that I will need to work with the following expression:

rangesum(above(sum(total <MTHSORIG> aggr(sum(CHRGOFFAMT),VINTMTH,(MTHSORIG,numeric,ascending))),0,rowno()))

This expression gives a single running sum (like the Total row).  However it continues to include VintMths even when there is no data.  Thus as the number of MthsOrig increases, VintMths should drop off one-by-one, and the "Total" should also drop as VintMths drop off.  In the example above, VintMth 201303 has no data for MthOrig = 9, and is thus not counted in the total.  The expression I have included here would continue to include VintMth 201303.

I am guessing that I will need to include some kind of set analysis that picks up a VintMth/MthOrig combination only when VintMth + MthOrig < CurrMth.  (The example above won't work with this test as I did not include enough MthOrigs).

Assuming that I need to include a set analysis, do you have a suggestion on the proper syntax?

Sorry, I can't find a way to attach an app.  I will keep looking - please advise if you like.

MVP