Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
So everything else looks good and you are only aiming to get the total row? Is that what you are trying to do?
Correct.
The formula above does give the correct running sum of the individual amounts, but what I really need is the total row.
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 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).
What do you see right now? Would you be able to share a sample to check this out?
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.
Check this link out
Are we still working on the pivot table or is this for the line chart?
The line chart. I was just trying to show the numbers behind the chart.