Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
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
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?

Labels (1)
16 Replies
sunny_talwar

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
Author

Correct.

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

sunny_talwar

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
Author

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).

sunny_talwar

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

Not applicable
Author

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.

sunny_talwar

Check this link out

Uploading a Sample

sunny_talwar

Are we still working on the pivot table or is this for the line chart?

Not applicable
Author

The line chart.  I was just trying to show the numbers behind the chart.