Pivot Table that displays the latest sum while rolled up

dtewfik — Sat, 16 Nov 2024 01:48:08 GMT

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:

Name	LoanDate.autoCalendar.Year	LoanDate.autoCalendar.YearQuarter	Other	Debt
	Totals		-555,435,602	-
	2013		0	0
	2014	Totals	0	0
	2014	2014-Q1	0	0
	2014	2014-Q2	0	0
	2014	2014-Q3	0	0
	2014	2014-Q4	0	0
	2015	Totals	-2,114,611	0
	2015	2015-Q1	0	43,345,899
	2015	2015-Q2	0	590,500,872
	2015	2015-Q3	-1,084,276	586,421,712
	2015	2015-Q4	-1,030,335	1,243,910,175
	2016	Totals	-4,115,887	0
	2016	2016-Q1	-1,029,745	1,233,678,172
	2016	2016-Q2	-1,029,305	1,211,785,467
	2016	2016-Q3	-1,028,714	1,195,205,104
	2016	2016-Q4	-1,028,123	1,177,624,661
	2017	Totals	-3,945,555	0
	2017	2017-Q1	-1,011,780	1,156,355,563
	2017	2017-Q2	-946,434	1,146,050,675
	2017	2017-Q3	-899,782	1,131,455,196
	2017	2017-Q4	-1,087,559	1,132,398,999

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

LoanDate.autoCalendar.Year	LoanDate.autoCalendar.YearQuarter	Other	Debt
Totals		-555,435,602	-
2013		0	0
2014	Totals	0	0
2014	2014-Q1	0	0
2014	2014-Q2	0	0
2014	2014-Q3	0	0
2014	2014-Q4	0	0
2015	Totals	-2,114,611	1,243,910,175
2015	2015-Q1	0	43,345,899
2015	2015-Q2	0	590,500,872
2015	2015-Q3	-1,084,276	586,421,712
2015	2015-Q4	-1,030,335	1,243,910,175
2016	Totals	-4,115,887	1,177,624,661
2016	2016-Q1	-1,029,745	1,233,678,172
2016	2016-Q2	-1,029,305	1,211,785,467
2016	2016-Q3	-1,028,714	1,195,205,104
2016	2016-Q4	-1,028,123	1,177,624,661
2017	Totals	-3,945,555	1,132,398,999
2017	2017-Q1	-1,011,780	1,156,355,563
2017	2017-Q2	-946,434	1,146,050,675
2017	2017-Q3	-899,782	1,131,455,196
2017	2017-Q4	-1,087,559	1,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

topic Pivot Table that displays the latest sum while rolled up in App Development

Pivot Table that displays the latest sum while rolled up