Graphing data from multiple sheets as a series

[Siesser]

I'm not sure how exactly to go about creating this graph, and have been trying to learn about pivot tables, as I THINK that will be the simplest route...

We are a vet practice with multiple departments. Each month, I export data into a table that automatically counts new visits to each department by clients from all of our referral practices. I have a different sheet for each month's data, and some formatting to identify changes to the month prior. What I would like to add to this document, for my 2017 entries, is the ability to visually chart the data. The fields of this chart/graph would be:

Xaxis - Month (Jan, Feb, Mar, etc.)
Yaxis - Practice (realistically no more than, say, the top 30 or 40, but with the ability to change this to ten, or just a single practice if we wanted (part of why I was trying to use pivot tables))
Value/Data point - The total number of visits for that month by clients of each practice, and if possible, the ability to change the overall chart via a filter so that I can look at the number of visits for a specific department for the year instead (a dropdown list with Total, Emergency, Oncology, Surgery, etc.))

The end result would be a horizontal line graph which would allow me to observe increases and decreases in referral activity to specific departments (and the practice as a whole) from our various practices from month to month. I've toyed around with pivot tables, but can't figure out how to incorporate data from multiple sheets that I can dynamically change the focus of. I've tried making a pivot table for each and linking the common field of Referral Source (Practice), but was not getting the proper result. I'm attaching our 2016 data to make it easier for me translate this to 2017's workbook. Also, since data is generated as the year progresses, are there any specific steps or issues with updating/adding data to the 2017 chart each month?

Thank you in advance for any advice or help you all can offer!!

[dflak]

I'll take a shot at this. This spreadsheet suffers from a typical "ailment" that many spreadsheets suffer from: I call it SODS or spread out data syndrome. In this case it's not only spread out among the columns, but also among the pages.

If you don't mind a macro-enabled workbook, can come up with something to normalize all the data - then a pivot table would be effective. I am looking at the hidden data on one of the sheets for columns A through G. I assume this is the downloaded data. One thing that would help a lot is if you can provide a "map" of what billing codes come under cardiology, internal medicine, etc. I can do some reverse engineering from the formulas.

Also I noticed that some of the dates in the August sheet are in July. I'll assume that's a date of service and the reason it's in August is that August is the billing or collection date. Which date do you want to collect this data under or do you want to collect it under both.

Finally, can you take a bit of time to explain how you collect and import the data and if you want to replace the monthly data (we are coming up on April - do you want to replace old April) or to keep it so we should have tabs like Apr-16 and Apr-17? The reason I ask is that maybe we can also automate or semi-automate the data collection process.

[Siesser]

Thanks for the response!

I can't say that I "mind" having a macro enabled workbook, but it's not something I'm familiar with. I can become so, though, if it's something that will make things easier down the road.

Columns A-G are a trimmed down portion of the information returned when I run a monthly report for all billed entries that fall under "PS," PS being "Professional Service." Amongst the many services billed out, all consults fall under that category of Billing item. That report generates all of the information in columns A-G plus some others about the client/patient that aren't relevant to the referral clinics themselves.

A few codes are used, as there are different kinds of consults (discounted, employee, etc.):
Department: Bill Code
Cardiology: PS1580, PS1582, PS1589 (We lost our cardiologists, so these always returns a zero consult result)
Internal Med: PS1525
Oncology: PS1556
Surgery: SxCNSLT
Rehab: RH1000, RH1001
Emergency: PS1126, PS1127, PS1128, PS1150, PS1180, PS1240

Any conflicts in dates are likely either an error on my part (start/stop time of report), and shouldn't be considered significant. If having excel perform its own sorting based on date makes things easier for creating the final document, I have no problem with that, but historically I've been manually importing the data into each month's sheet as a result of a query from 1st to end-of-month.

The data of this sheet I'd like to keep as is, for historic analysis. I've started a 2017 workbook using the same process in the interest of not having 12 tabs x number of years in a single workbook. If there too is a way to slim this down such that everything may remain in a single workbook, or even allow us to observe trends year to year or compare January of last year to this year without having to have multiple windows open, then all the better. I guess since the tabs can just be hidden, compiling it all into one isn't a bad idea...

[dflak]

Give me a sample of what your "raw" data looks like. I'm mostly interested in the headers, but I'll take sample data if it is not sensitive. I think I can figure out from the existing workbook how to organize it.

I am planning on bringing in the raw data, normalizing it and putting it into a big data sheet that you probably aren't interested in looking at. What I can do is give you a dashboard that will give you a monthly list, year to date summary or running 12-month. Once you have the data imported in a normalized format, the rest of the reporting is extremely flexible.

[Siesser]

Sure, that should be ok. The software presents this specific report via web browser, where I then save the web page and drag into excel to create a spreadsheet. Attached are January and February 2017 raw data.

[dflak]

Here is the "Alpha" report. Basically, it's a prototype to see if I'm on the right path and to act as a point of reference for enhancements.

On the control panel, Cell B1 is the current date. Cell B2 can be used to override this date, but should only be used in extenuating circumstances that I will describe below. Cell B3 is the date that gets used for the report. This cell determines the month for which the data are to be imported. This month is shown in cell B5.

So in a couple of days, Cell B1 changes to April 1st, The month changes to March and the warning goes away since March data has not been imported.

Cell C7 is the calculated name of the file to import. If for some reason, the file you need to import doesn't have this name, you can override the name using cell B8. Be sure to add the proper file extension. Cell B9 has the name of the file the program will use.

Under normal circumstances, the date and file name is correct, so all you have to do is click on the Get Data Button. When the data is read in, the spreadsheet detects that there is data for this month, and the warning message appears. It's not likely that you will import the same file twice.

What I did for the test was fill in cell B2 with a date in February which made the report month January which gave me the correct file name for January's report. Always change the date first if you have to. Again, I don't think you'll ever have to do this as long as you remember to download once a month.

The monthly report replicates what you have on the former report. I included some slicers (basically cool-looking filters) so you can further fine tune the report.

I added an extra pivot table: Codes needing definition. These are codes that appear in the data, but are not linked to an area. You can copy these, paste them into cell B16 on the Parameters sheet and fill in the Blanks in column B.

When you import the data, the pivot tables are refreshed automatically by the program. If you add new codes to the codes table, you can either refresh the pivot tables manually or use this button.

[Siesser]

This is MILES beyond what I could have hoped for! (and far beyond what I could have done). I'm toying around with it now to make sure I understand how everything works - on Monday I should be able to test adding a new month's data (March's). Under "Codes needing Definition" is PS1150 - an emergency code which appears to be properly identified as such under Parameters, but isn't being factored. This one is causing a significant drop off in ER numbers for the individual practices - any idea why that would happen and how best to fix/avoid that conflict?

[dflak]

Look at the table in Columns A & B on the Parameters Sheet. Add whatever you need to this table. This table is what the program uses to look up an area. I just noticed something. Some of the Codes I have in there have a leading space. Remove the leading space. The strings need to match exactly. Fixing this should fix some of your problem.

[Siesser]

Importing March's Data was a breeze! The last hurdle I'm running in to is figuring out how to sort. Alphabetical by practice makes sense for a majority of how I'll be using the data, but if I wanted to see the top 10 or 20 by department or overall, I'm not finding an easy way to perform that sort on the data that is returned.

[dflak]

Go to the monthly report sheet. Click in the pivot table in column A. Sort A-Z. or click in the pivot table in the Grand Total column Sort largest to smallest.