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).
I am looking for the "Total" row. I have found that the following formula gives me the desired result for each "VintMth":
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.
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).
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.