Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 250
  • Last Modified:

Add a computed field to a Pivottable for Month-Year

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
Ed_CLP
Asked:
Ed_CLP
  • 2
  • 2
1 Solution
 
Rory ArchibaldCommented:
Can you not simply group by Year then month? That would produce the same outcome.
0
 
Ed_CLPAuthor Commented:
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
 
Rory ArchibaldCommented:
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
 
Ed_CLPAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now