Pivots & REAL Running Totals - I NEED U MR EXCEL!

[tgartner]

I am trying to prepare a report that provides a running bank balance for a large amount of bank account data. I only need 2 fields [Date] and [Net Amount] to make this work (or so I think)

I of course am using a pivot table for this and I am trying to get it in this format:
2006 2008 2009 2010
Jan
Feb
Mar
Apr
May
Jun
Jul
Aug
Sep
Oct
Nov
Dec

To get this format, I am grouping:
- the row data field [Date] as MONTH
- the columnar field [Date] as YEAR.
- set the values field [Net Amount] to show values as "Running Total In" and the base field as [Date]

The result works great but it ALWAYS resets to ZERO after each year. I know this has to do with the grouping I am using. But for the life of me I cannot figure any trick to get this to work. HELP.

BTW, I luv u man! your stuff here is awesome!

 

[GlennUK]

If you are happy to do this outside of the pivottable ( which would be used to generate the running total for a single field vertically ), and can add an extra field to the source data to give the month of the data ... like

=DATEVALUE("1/"&TEXT(A2,"mmm/yy"))

then you could build that reporting layout using GETPIVOTDATA and creating the arguments from your row and column headings of the reporting block.

Is that any use to you?

 

[tgartner]

Hey Glenn,

Thanks for your reply. I appreciate the help mate.

I tried your idea. However I am struggling to assess what this really gives me. All the GETPIVOTDATA function does is provide outputs from an existing pivot of summated data and rewrite the same data in a preformated columnar and row headed table.

How exactly do I get a running sum with this new method?

Thanks
Trevor

see image

 

[GlennUK]

You set the running total for the date in a pivot ... without any grouping in a single list summarised by the new month field. That's the running total you want, but it's not the shape you want. That's why you use GETPIVOTDATA ... to reformat it into the preformated columnar and row headed table. That's the idea behind what I suggested.