Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win


Pivot Table help

Posted on 2011-03-15
Medium Priority
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
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
LVL 19

Expert Comment

by:Arno Koster
ID: 35148244
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

ID: 35150833
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

ID: 35156501
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 2000 total points
ID: 35156813
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

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

Featured Post


Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

: Microsoft Office Collaborate for free and online versions of Microsoft  Word, Excel, Powerpoint, OneNote, Onedrive , Email, Calendar etc. In short we can say that Microsoft office is a suite of servers, applications and services developed by  Micr…
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

597 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