Solved

Pivot Table help

Posted on 2011-03-15
5
376 Views
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.
Thanks!!
Forecast--2-.xlsm
0
Comment
Question by:singleton2787
[X]
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
5 Comments
 
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


0
 
LVL 12

Expert Comment

by:telyni19
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?
0
 

Author Comment

by:singleton2787
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.
0
 
LVL 12

Accepted Solution

by:
telyni19 earned 500 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.
0
 

Author Comment

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

Featured Post

Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Macro to convert table into pivot tables 25 71
can excel files with data model work in excel 2010? 3 40
drag and drop a row in excel 2010 5 44
sort time order 10 45
: 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…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

732 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