Calculating Row/Column Headers in PivotTable

[mikelee101]

Hello,
I have a table of data, the first two columns of which are Date and
Transaction Amount. What I'm trying to do is put together a table that
will summarize total transactions by month/year. My goal is to have 1
through 12 (months) as the column headers, and have the years be the
rows. Then I could use Sum of Transaction Amount as my data field.

However, I can't seem to figure out how (or if I can) force a pivot
table to base both the rows and columns on the same data point. I've
right clicked on the column headers and selected both Calculated Item
and Calculated Field. When I try to enter:

=Month(Date) 'where Date is my field name

I get an error that says "References to multiple item names per field
cannot be included in PivotTable formulas."

If it's possible to do this with a pivottable, please let me know. I
know I can add a two new columns to my underlying data and use them to
calculate the month and year of each individual data point, but that
would change the layout of my table and would require rewriting of some
macros, so I'd like to avoid that if at all possible.

Thanks,
Mike
McKinney, TX

 

[Roger Govier]

Hi Mike

Make the Date field a Row Item
Right click on your date field in the PT.
Group and Show Detail>Group>select both Years and Months
You will now see Years then Date in the Row area. Grab the Years label,
and drag it to the Total field and you will then see

Months 2006 2007
Jan
Feb

Alternatively, drag the date field to the column area, leaving Years in
the Row area and you will see

Jan Feb Mar
2006
2007

 

[mikelee101]

Roger,
Wow. Worked like a charm.

I could have tried for years and never figured that out.

Thanks a million for the help.

Mike

 

[Dexsquab]

Looks like this solves my request, too.

Many thanks

 

[Roger Govier]

Hi Mike

You (and Dexsquab) are very welcome. Thanks for the feedback and glad
its sorted your problem