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

# 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?

• ###### 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?

• ###### 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.

• ###### 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)))

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

• ###### 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?

• ###### 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.

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

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

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

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

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

I apologize, but I still don't completely understand what you are looking to get. You have two dimensions in your chart, where are you hoping to see the total row? Is that going to be a separate line for total? Is this chart not right? What are you looking to get here?

If you can provide the numbers which you expect to see based on the data in your sample app, that will be very helpful to understand what might you be trying to do.

Best,

Sunny

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

I am looking to create a single additional line that carries this total information.

I have an excel spreadsheet showing the additional information.  Can I send this to you directly - I'd rather not post it on a public forum.

Thanks.  Jeff.

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

I have added you as a connection. You can send me a private message. I will share my email on the Private message with you

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

I am sending this from the Inbox section, but am not finding an attachment icon.  Am I doing this correctly?

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

Just send me a private message. Once you do, I will give you my email to send the file. There is no way to attach the file through PM

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

I apologize for my ignorance.  How do I send you a private message?

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

Sunny T,

Can you pick up this thread again?