Qlik Community

App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
Customer & Partners, DEC. 9, 11 AM ET: Qlik Product & Strategy Roadmap Session: Data Analytics REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
dtewfik
Partner
Partner

Pivot Table that displays the latest sum while rolled up

Hi!

I'd like to have a pivot table where one of the columns (Debt) is modeled so that it is the the cumulative amount for that month.  Because the data is formatted this way, I can't just sum up the entire year like you can with 'Other'.  Here's what it looks like now:

NameLoanDate.autoCalendar.YearLoanDate.autoCalendar.YearQuarterOtherDebt
 Totals -555,435,602-
 2013 00
 2014Totals00
 20142014-Q100
 20142014-Q200
 20142014-Q300
 20142014-Q400
 2015Totals-2,114,6110
 20152015-Q1043,345,899
 20152015-Q20590,500,872
 20152015-Q3-1,084,276586,421,712
 20152015-Q4-1,030,3351,243,910,175
 2016Totals-4,115,8870
 20162016-Q1-1,029,7451,233,678,172
 20162016-Q2-1,029,3051,211,785,467
 20162016-Q3-1,028,7141,195,205,104
 20162016-Q4-1,028,1231,177,624,661
 2017Totals-3,945,5550
 20172017-Q1-1,011,7801,156,355,563
 20172017-Q2-946,4341,146,050,675
 20172017-Q3-899,7821,131,455,196
 20172017-Q4-1,087,5591,132,398,999

 

When rolled up, it should display the debt of the last month.  For example:

LoanDate.autoCalendar.YearLoanDate.autoCalendar.YearQuarterOtherDebt
Totals -555,435,602-
2013 00
2014Totals00
20142014-Q100
20142014-Q200
20142014-Q300
20142014-Q400
2015Totals-2,114,6111,243,910,175
20152015-Q1043,345,899
20152015-Q20590,500,872
20152015-Q3-1,084,276586,421,712
20152015-Q4-1,030,3351,243,910,175
2016Totals-4,115,8871,177,624,661
20162016-Q1-1,029,7451,233,678,172
20162016-Q2-1,029,3051,211,785,467
20162016-Q3-1,028,7141,195,205,104
20162016-Q4-1,028,1231,177,624,661
2017Totals-3,945,5551,132,398,999
20172017-Q1-1,011,7801,156,355,563
20172017-Q2-946,4341,146,050,675
20172017-Q3-899,7821,131,455,196
20172017-Q4-1,087,5591,132,398,999

 

Right now I'm using the formula:

 

aggr(sum(Debt), [LoanDate]) 

 

And it's clearly not working for the totals. Any help is appreciated

Labels (3)
0 Replies