Solved

Pivot Table help

Posted on 2011-03-15
5
372 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
  • 2
  • 2
5 Comments
 
LVL 19

Expert Comment

by:akoster
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

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,…
Drop Down List with Unique/Distinct Values (enhancing the Combo-Box with a few steps and a little code) David miller (dlmille) Intro Have you ever created a data validation list from a database field or spreadsheet column (e.g., Zip Codes or Co…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

919 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

16 Experts available now in Live!

Get 1:1 Help Now