Pivot Table help

Posted on 2011-03-15
Last Modified: 2012-05-11
I am trying to sumarize and analise the following:
·         Forecasted Account migrations from 12/1/2011 to 6/1/2011 (actuals and planned)
·         Planned Account migrations 7/1/2011 to year end 2011
·         # of Accounts shifted (dates for migration changed from an earlier one) from Dec Go-Live to Feb Go-Live
·     Sum number of accounts that made their target, sum of accounts that did not

I cannot get a pivot table to display any of this info, much less look good for end users.
Question by:singleton2787
  • 2
  • 2
LVL 19

Expert Comment

Comment Utility
for the top two points, you'd best use the autofilter and not a pivot table.

eg. to filter for migrations from 7/1/2011 to end of 2011 take these steps :

copy-paste the complete "wave comparison" worksheet and rename it to "Planned migrations"
select appropriate autofilter (eg. F), press button with downwards pointing triangle
go to "date filters" - "between"
enter starting date (7/1/2011) in the first date box ("is after or equal to")
enter end date (31-12-2011) in the second date box ("is before or equal to")
press ok

LVL 12

Expert Comment

Comment Utility
Your filters on the pivot table are filtering out everything. There are no records where the Feb-Go-Live or March-Go-Live columns are blank. If you remove those filters, then there are 173 items where the Dec-Go-Live column is between Dec 2010 and June 2011. Does that help?

I think you will need to be clearer about defining the information you need. For instance, what is the definition of an item that "made its target"? What is the target? If migrations are planned for late 2011, you don't know yet if they have happened on time, right?

Author Comment

Comment Utility
tely, you are correct...we don't know if they made the target for late 2011 yet. Let me see if i can re-summarize what I am trying to do:

# of accounts that were planned for 1st Half of 2011 (For Dec Go-Live, and the others, seperately)
# of accounts the are planned for 2nd Half of 2011 (for each Go-live column, as it seems to change)
# of accounts that were moved to a later date (once again, seperate for each Go_live plan)
# Average (or sum?) of accounts that did NOT make thier target deployment date, but were moved to another Wave (deployment)

Ideally, I would want to be able to identify each account that was moved in in it's target deployment date. But since there are 4K of them, I might just have to average them? Another issue is that some of the target deployment dates for the accounts move to a future date, then get pushed to a sooner date.
LVL 12

Accepted Solution

telyni19 earned 500 total points
Comment Utility
I still don't understand the process here. Is this a three-stage activation, or just a one-stage? In other words, is there just one actual deployment date for each account, or three? Do we assume that if the Dec-Go-Live date is missing or later than December 2010, then the account missed its December target date and the actual date is one of the other columns? So then why are there missing dates in the Dec column but not in either of the other two? And why would the date in the other columns be earlier than December 2010 if the Dec date is missing? Are you trying to summarize actual dates or target dates?

I'm going to suggest in general though that you set up a few extra columns in your data, such as Actual Deployment Date, Dec to Jan Diff, and Feb to Mar Diff, where the last two are the actual difference in months between the dates in the relevant columns. Your Date Diff columns are absolute differences in months, which won't tell you whether the date moved forward or backward, so you can't use them to tell whether the account made its target date or not.

If you set up your definitions and calculations clearly, you'll have a much better chance of being able to show the information you want in your pivot table. I'm happy to help, but I just don't understand yet what your data means.

Author Comment

Comment Utility
You are 100% right... I need to go back and rework this. I will repost after I get some clarification

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Suggested Solutions

Introduction This Article is a follow-up to my Mappit! Addin Article (, it was inspired by an email posting I made to EUSPRIG (, I will briefly cover: 1) An overvie…
This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

743 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now