Advanced Run Sum Pivot

[tgartner]

I need to create a running sum in a pivot, which in itself is not hard to do as long as the running sum is only columnar or row based. What if the runnung sum is on both... let me explain... lets assume that the column headers in the pivot are dates grouped by month and year (Jan, Feb, Mar/ 2000, 2001, 2002, etc). And the row headers are the days of those months (1, 2, 3, 4, 5, 6...). and the value field is the daily net change in a bank account (-$34, $125, etc, etc). Is it possible to create an entire running account balance by day by month (and year) in a pivot?

2010 2011
Jan Feb Mar... Jan Feb Mar...
1 ($34) ...
2 125 ...
3 ...
4
5
6
...
6

 

[Fazza]

Hi,

I am a little confused: the requirement is for a running sum and the value field is the daily net change -34, 125, etc. I would expect the answer is then -34, 91, etc whereas the sample result shows as -34, 125, etc. I have answered as I expect & assumed the sample result (in the question) is wrong. If this isn't correct, please clarify - or modify below to suit.

I started with sample data in two columns: MyDate & Value. I gave the entire source data the defined name (normal, non-dynamic) of MyTable. Save the file.

From a new file, start the pivot table wizard ALT-D-P taking the external data option at the first step and follow the wizard to the end taking the option to edit in MS Query. Hit the 'SQL' button and replace the SQL by

SELECT Day(A.MyDate) AS [MyDay], Month(A.MyDate) AS [MyMonth], Year(A.MyDate) AS [MyYear], SUM(B.Value) AS [MySum]
FROM MyTable A, MyTable B
WHERE A.MyDate >= B.MyDate
GROUP BY A.MyDate

OK to enter this. (And OK to any messages you get.) See a dataset. Hit the 'open door' icon to exit MS Query & make the pivot table. MyDay as a row field, MyYear and MyMonth as column fields, sum of MySum as the data field showing the running total. If you like, the resultant worksheet containing the pivot table can be moved into the source data file.

regards, Fazza