• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Best Way to Change Existing Report Data to Use in a Pivot Table/Chart

A

ann99m

New Member
We have a large file with lots of data on errors that are made. They are tracked weekly, and different error codes associated with specific departments. Sometimes errors in a certain category will be split between 2 departments.


We take all of our errors and use those sums and the total volume of work for each week to get an error rate in errors/1000 parts (this helps to get accurate measurements since our volume can vary widely). For example, if a department has 10 errors and we had 6000 orders that week, their error rate would be 10/(6000/1000) = 1.67 errors/1000.


We create charts with this info. A main chart for the overall company, and then separate charts for each department. The charts usually show the past 4-5 weeks and has a goal line based on 10% less than the error rate from the same week the previous year (a rolling average actually). We also show last year's actual error rate for the same week. (Our business is very seasonal).


Currently, the data is set up with Week of 1/1 - 1/7, 2010 in Row 1, Column A. Then 1/8 - 1/15 in Row 1, Column B, etc. This goes on all the way to the end of the year. Then there is a year end summary and then 2011 will be shown in the same way. The volume for each week is show in Row 2 (actually in the sample file I uploaded the weeks ar in Rows 2,3,4 and Volume is in Row 5). Then the Error codes are shown going down column BD. in Column BE (next to them, is the Dept. to which that particular type of error is charged.


Finally, totals for each department are underneath all this, along with their calculated goal and % changes. It is hard to explain so that is why I uploaded the file.


The file is very hard to manipulate due to all of the hidden rows (usually only the last 6 weeks and upcoming 6 weeks columns are shown, but I unhid them for uploading.


If possible, we would love to get this file into a format that we can create pivot tables and charts from.


Any ideas are greatly appreciated.


Thank you!

Ann


Sample file is here: https://rapidshare.com/files/3927609431/RNC_Report-Sample_for_Upload.xlsx
 
I understand your pain with this one......


Here's the deal: You've got a huge data entry sheet where all of your calculations and some light presentation is going on. It's not that you can't do it this way, The problem comes down to what you said: you can't really do much past look at it.


I would try to organise your data on another worksheet. Try like so:


Week Department ErrorCode ErrorCount


This would support charts, any other presentation layer you want, plus a pivot table for longer term analysis. Obviously, you may need an intermediary calculation sheet, but you'll probably be able to build a more slick ass report. If will also be vastly easier to maintain.


If I get a couple of minutes, I'll play with it and show you what I mean.
 
If I'm reading this right:


The each error code has only one or possibly 2 departments who can make that particular error, right?
 
Yes that's correct. This report was created before I got here. I'm just glad I don't have to actually do the data entry on it!


I would like to transition it to an easier/cleaner format, but struggled with figuring out how to do so. Thanks for taking a peek at it!


We have multiple reports around here that are structured in a similar way, so if we can get this one fixed up we may be able to use it as a template for several others.


Thank you again!
 
Ok, I just checked further with the person who does the report each week. We get the codes when the remake work is re-billed. The error code is entered into the billing/order system and then a report is run each week.


The report generates an output that can be printed or viewed on screen. Example uploaded here: https://rapidshare.com/files/3896443511/error_report_sample.pdf
 
Really. That's kind of a pain. I'm surprised you can't get more 'raw' access to that data.


Sometimes the biggest pain about this stuff are those intermediary steps - like having to have a data entry person cracking this stuff out.


Let me see what I can think up for this.
 
I think originally the report was designed as only the report generated by the billing system. Then they wanted to start comparing to past years/months/weeks so an excel report was created. Then charts were added. The problem with the charts is that they have to be manually updated and the specific weeks/data has to be hand selected since they are not in a pivot format.


I don't think the plugging in of the data is too bad from the report. It's just 20 or so numbers once a week. The real pain would be in updating each of the charts and hiding/unhiding stuff all the time.
 
I guess in terms of charts, I meant it is a pain because someone has to change the data source every week for about 8 different charts. Then, if you want to see 1 year, 1 month, 1 quarter, etc. you have to unhide those columns and then select that data. To me, it's almost more trouble than it's worth. If I really needed the info for a quarter I would do it, but if I was just curious and wanted to take a quick look at quarter results it takes much too long and I would never do it.


Thanks again for helping out with this.
 
Sorry it's taking me so long to help you with this. It's been a long few days, I'm effectively brain dead - like totally toast. But I don't want to leave you hanging.


http://dl.dropbox.com/u/1275899/RNC_Report-Sample_for_Upload1.xlsx


So evaluate this on concept only and let me know if this is about where you're heading. The execution is really rudimentary (it took maybe 10 minutes to put it together) and it's only partially realised against what you already have.


All that went into it:


1. A dynamic range built on the row with your dates.

2. A cell with data validation pointing to that dynamic range

3. A sheet that's specifically for getting the data we want to present. Really, it's only using index/match and hlookup to get the data.

4. A chart that's pointing to the data in 3.


There's nothing stopping you from just repeating the stuff in 3 over and over again to get more data and make more charts. Or use the same concept to create a 'cut sheet' with only our relevant periods raw data.


I tried to do this in a fashion that doesn't require a lot of modifications to the source data. In truth though, 99.99% of the time, my source data and any calculations from it are elsewhere in the work book as to not actually be part of the deliverable. This has numerous benefits, not the least of which is having the data structured in the easiest possible way for you to do stuff like this with.
 
Hi Dan,


I looked at your file and I'm a little confused.


First, I did try setting up a file like you suggested several days ago like:

Week Department ErrorCode ErrorCount


however, this would entail having to type in the department and error code every time, as well as the department, each week. i.e.


Week Dept. ErrorCode ErrorCount

1 A Error 2 3

1 A Error 3 5

1 A Error 5 1

1 B Error 6 4

1 B Error 7 4

1 B Error 8 2

1 C Error 9 3

1 C Error 10 2

2 A etc. etc.

etc.


To me this seems like more work, maybe I am missing something.


Also, I looked at your file and I am a little confused, especially by the purpose of the Sorts tab. It seems like you are using that to lookup/pull the info for the charts. If we used this, wouldn't we have to update this each week instead of the chart data selection?


I do appreciate your help, just not sure how it works.


Thanks,

Ann
 
------

however, this would entail having to type in the department and error code every time, as well as the department, each week. i.e.

------


Yes. I assumed (wrongly) that this data came to you in a more potable format. If not, it would be a bit more of a pain to make it work that way. That sort of "database" type feel for the data is idea for working with pivot tables. It's a little bit weirder to do with your current data, because it's basically already a cross tab now.


-----

Also, I looked at your file and I am a little confused, especially by the purpose of the Sorts tab. It seems like you are using that to lookup/pull the info for the charts. If we used this, wouldn't we have to update this each week instead of the chart data selection?

-----


No.


The data validation cell tells the data on the sorts worksheet which data to extract into that narrow fixed range. The charts are pointing at the fixed range. So, as you change the data validated cell on the page with the chart, you'll find the chart updates automatically.


And, as I had mentioned before, the list driving the validated cell is dynamic, so if you were to add another date and some dummy data, you'll find that that data automatically is available to you on the charts page.


If you haven't messed with it yet, the validated cell is the one in yellow.
 
Ahh, OK thank you. I missed the data validation cell previously. The way the chart changes is great. I need to read up on dynamic data and charts.


Just a thought, is there anyway to use index and match to pull ranges and set them up in a way that we could build a pivot table off of?
 
It's actually really easy.


Dynamic ranges are either achieved through the offset() formula, (OFFSET(starting cell,rows, cols,height,width) ). You end up replacing height or width with a count or count a function that counts the number of active cells in your data. Some people find it to be a bit of a headache because it's such a flexible concept.


There is a shortcut method that doesn't involve the formula you can use with tables, which fill the purpose of dynamic ranges (as well as a thousand other uses), but you may find yourself having to take a bunch of extra steps to make the shortcut work.


The charts are really as simple as getting a lookup to work. There's no trickery to it. It's that simple.


I'm not real certain about how you would get a good pivot table out of this. The trouble is that your data is already in a cross tab.


Maybe one of the local pivot table guru's can come up with something?
 
Thanks Dan. it took me a little bit of research to figure out, but I learned how dynamic ranges are based on formulas. then i just copied and modified the formula you had for the dynamic range. I also worked on adjusting your formulas to get the table I need.


The one aspect that is a little tricky is figuring out how to show last year's info. As you may have seen in our graph, we have our current errors shown as bars. Our goal for this year is a green line chart, and then we show last year's errors in blue. To me it's not vital, but I can see some people complaining if we don't have last year's info also.


Thanks again for the great info. I showed your dynamic chart to the lady who has to do the report and charts and she was very excited and impressed.


As to "local pivot table gurus", I'm afraid that would be me! I'm wondering if I set up some formulas to pull info from the main database to set it up in a pivot table friendly format. Will post back if I figure it out. Thank you!
 
It's mostly the same way. Create some ranges and apply lookups to fetch the same data from the previous year and parse it out in that 'sorts' sheet. Add it to your chart.


It's a little bit of work, but probably spending an hour putting this together now will probably save people eons for a while.


I'm not sure how you would use formulas for pulling data out of a database. You may want to ask your IT guys if they'll let you access the db via MS Query. The answer is, more often than not, 'no', but it's wortha shot.
 
You must log in or register to reply here.
Back
Top