Solved

Excel 2007 set drill down level

Posted on 2011-02-15
12
709 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
 

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
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
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

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Sparklines have been introduced with Excel 2010 and are a useful tool for creating small in-cell charts, used for example in dashboards. Excel 2010 offers three different types of Sparklines: Line, Column and Win/Loss. What it does not offer is a…
INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
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.

759 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

20 Experts available now in Live!

Get 1:1 Help Now