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
Solved

Excel 2007 set drill down level

Posted on 2011-02-15
12
713 Views
Last Modified: 2012-08-13
I need some assistance with a pivot table using Excel 2007.  I attached the spreadsheet I’m trying to work with.  I’m trying to track lead_goals with leads_created.  The data comes from a view in Oracle.  

My lead_goals data is compiled at the month level, for instance 110 leads is the goal for a particular location for Sept 2010 (you can see this in rows 95-129 in Sheet1).  However, my leads_created data is broken down to a finer level, by day created, source, current status, etc.  Therefore, the lead_goals data is repeated for every row that corresponds to that month.  

When I pull the data into a pivot table, Excel naturally wants to sum or count the lead_goals data (as well as apparently break it down by day, source, and status).  My customer knows that if she drills down below the month level with the leads_created data, the lead_goals data will not drill down.   I want to have leads_created and lead_goals in two separate columns in the, ‘data’ area (I’m not sure of the terminology).  

How do I tell Excel to associate the lead_goals value with the month level?  I don’t know much about pivot tables or VB code, but my background is pl/sql and a little java, so I’m open to whatever suggestions anyone has.  

Thanks!
mmr-spreadsheet---Copy.xlsx
0
Comment
Question by:tancat
  • 6
  • 6
12 Comments
 
LVL 12

Expert Comment

by:telyni19
ID: 34900925
Try right-clicking on "Lead Goals" and choose Max in the "Summarize Data By" menu. That will tell Excel to simply take the maximum value rather than the sum; if all the values are the same for that month, you will get that one value. Does that solve your issue?
0
 

Author Comment

by:tancat
ID: 34901073
That appears to get me closer.  However, when I roll up to the quarter or yearly level, it displays the max instead of the total.  I think I want to choose Sum, but I only want it to sum upwards from the month level; I want it to display null/empty when drilling down.  
0
 
LVL 12

Expert Comment

by:telyni19
ID: 34901489
This is the kind of complex query result that a single pivot table does not handle very well. I think you could do it with calculated fields in an Access query, which is able to pull in information from multiple tables and summarize the results to a much finer degree.

I actually started trying to construct it in Access with your data, but I ran into the problem that there appeared to be multiple goals for the same month and OID combination, even in the same year (35, 110, and 198 for September for 9462, for instance). In order for the query approach to work, there has to be a unique goal number for a particular combination of data, so that the goal can be associated with the month and OID in another table (which is one form of normalizing the data, by the way - it's usually much easier to do complex queries in normalized data, plus the data itself is more efficiently stored).
0
Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

 

Author Comment

by:tancat
ID: 34901755
The difference between 35, 110, & 198 is actually in the paid_type, which I (hopefully correctly) put as a filter at the top.  I attached the data that is in the lead_goals table.  

The data will ultimately reside in an Access database, but since I'm more familiar with Oracle, that's where I started.  The end result is supposed to be this spreadsheet accessible in SharePoint.  
goals.xlsx
0
 
LVL 12

Expert Comment

by:telyni19
ID: 34902024
I missed what you said about Oracle previously, but I'm the reverse, much more familiar with Access than Oracle. In general I would recommend making a different Oracle view, but I suspect no flat table will be able to give you data that can be manipulated the way you want in an Excel pivot table. If you can get Oracle to do the sorts of summary queries or pivot table results that Access queries can do, then you could do all the work in Oracle and just export the final result to Excel. An Oracle expert would probably have to help you with that approach though.

I tried again to get the results in Access to give you an example of how I would do it there, but I think I just don't understand the data properly. In the original data, what month is the MonthName representing? It doesn't match the FY_Month field. The Goals table doesn't have that field, either. Even in the pivot table, something doesn't make sense, because September is visible, but the Quarter number is 1. September is 3rd quarter, I would think.
0
 

Author Comment

by:tancat
ID: 34902123
Our fiscal year starts Sept 1, so first quarter is Sept, Oct, Nov.  It is easier to handle the data (fy_month) as a number (1-12) but for the final report they prefer the names of the month, so month_name is just a case statement that says 1=Sep, 2=Oct, 3=Nov, etc.  (fy_day starts with 1 on Sept 1)

I can produce summary queries and detailed queries in Oracle, but not both at the same time.  I could not figure out how to import anything other than a single table/view/query into Excel; I think this would work if I could import two queries into Excel - can that be done?  
0
 
LVL 12

Expert Comment

by:telyni19
ID: 34902273
Thanks for the clarification on the quarters. I will have to work on this tomorrow to put together an Access suggestion for you.

To answer your immediate question, you could certainly have two separate tables in different sheets in Excel, but you can't easily recombine them meaningfully on shared fields the way you can in a database. If you mean can you import two queries at once from Oracle into one Excel file, that I don't know. Access could do it though, especially with VBA code.
0
 
LVL 12

Accepted Solution

by:
telyni19 earned 500 total points
ID: 34909216
I really don't see a good way to create a pivot table with the flexibility you're specifying. However, I think you could show all the information you need in a clear way by adding some more fields. Take a look at the attached. The database is just there to show you how I used queries in Access to put the Goals table together with the original data and combine it into a flat table with the additional fields summing the goals by quarter and year. It also illustrates the calculated field in the final CombinedData query that produces the correct month name from the fiscal month number, so that information doesn't actually have to be stored in the base table.

Then in the Excel file, the final three columns are calculated there and added to the pivot table, which can then show (via max of the goal fields and sum of the percentage fields) the total goal values for month, quarter, and year as well as the percentage actually achieved.

I hope this helps. I know it isn't exactly what you asked for, but I'm hoping it helps you get to something that serves your purposes, because I'm not sure what you asked for would be possible without a good deal more manual construction.

The problem is just that if you have data split out by day, then every record has to have the associated value, which is problematic if you're summarizing by higher time periods. Even if you summarize all the data by month, you still can't use Sum on the goals because there are multiple items for different paid values, etc.
leadqry.zip
mmr-spreadsheet-updated.xlsx
0
 

Author Comment

by:tancat
ID: 34910422
This is looking good; let me check it out and get back to you, probably tomorrow morning because I have meetings the rest of the afternoon.  Thank you!!
0
 

Author Comment

by:tancat
ID: 34917399
This is absolutely perfect!  The marketing people will just love it – thank you, thank you, thank you!
   
More importantly, this is also be a great tool for me to learn from, since I’m still new to Excel and Access, and I learn so much better from example than from an explanation in a book.  

Thank you, telyni19!!
0
 

Author Closing Comment

by:tancat
ID: 34917425
telyni19 went beyond and above to help me out with this problem (and of course, make me look good!).
0
 
LVL 12

Expert Comment

by:telyni19
ID: 34917600
Thanks, glad it helped!
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Pivot charts - adding refresh button 6 40
Need Help with Mapping and Toggling 3 20
Turn date into age 17 31
Testing for the presence of text in a range of cells 3 10
Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

792 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