Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

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

Pivot Table help

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
singleton2787
Asked:
singleton2787
  • 2
  • 2
1 Solution
 
Arno KosterCommented:
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
 
telyni19Commented:
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
 
singleton2787Author Commented:
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
 
telyni19Commented:
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
 
singleton2787Author Commented:
You are 100% right... I need to go back and rework this. I will repost after I get some clarification
0

Featured Post

[Webinar On Demand] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

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