Solved

Add a computed field to a Pivottable for Month-Year

Posted on 2011-03-15
4
242 Views
Last Modified: 2012-05-11
I'm trying to add a computed field to a Pivottable for Month/1/Year.  The data used to create the Pivot only has columns for Month and Year.  I need to group the data by MM/YY so I tried to add a formula field month_date&"/1/"&year_date but it displays it as a SUM of MyFieldname with #VALUE! for the cell values.  
The version is Excel 2007.
0
Comment
Question by:Ed_CLP
  • 2
  • 2
4 Comments
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 35137939
Can you not simply group by Year then month? That would produce the same outcome.
0
 

Author Comment

by:Ed_CLP
ID: 35137987
No, that's what I do now.  I need to group/sort/chart the data  as MM-YY.  I'm reporting on a rolling 6 and 12 month dataset that spans 2 years.   I'm finding that when I insert a formula field the Pivot only has options to sum,avg,count etc for the formula field and column.
0
 
LVL 85

Accepted Solution

by:
Rory Archibald earned 250 total points
ID: 35138114
I'm still not really clear why you can't report on what you have, but you would need to add a column to the underlying data I think. Neither a calculated field nor calculated item will work for this.
0
 

Author Comment

by:Ed_CLP
ID: 35138165
Yeah, that's the way it looks - I'll have to add the computed value to the source worksheets.
I'm trying to duplicate some existing reports and they aren't grouped by year and then month, other wise the Pivottable would work fine.  

Thanks
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

685 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question