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
