A Day in the Life: Ad Hoc Requests & Automated Tools

Posted by

As a data analyst, my job is to get people the information they need.  Instead of just responding to each request separately, my goal is to create tools that allow users to ask and answer questions on their own.  Part of this is out of laziness and not wanting to do the same thing over and over again, but it’s also because this gives my users direct and timely access to the information they need, when and where they need it. To quote P-Funk, “give the people what they want, when they want, and they want it all the time!” 

For instance, if the Chief Quality Officer (CQO) is presenting to the Hospital Advisory Committee and they ask him a specific question about the data, my goal is that he could answer it on the fly with one of my pre-built dashboard tools, preventing follow-up emails and meetings, and making our department look great.

Of course, it isn’t possible to anticipate every request, and there’s a balance between what should be kept ad hoc and what should be turned into an automated and flexible investigative tool.

A semi-standard view of surgical infections
Recently, I got a request from the CQO to show Surgical Site Infection (SSI) rates for a particular time period that didn’t line up with how we normally report dates.  We’ve talked a lot about SSIs (pt 1, pt 2, pt 3), so I’m not going to rehash the particulars of the data and metrics.  The CQO wanted to see a year, from April to March, but we normally report via our July to June fiscal year.  This wasn’t the first time I’d had such a request, so I decided to make an automated tool.

Skipping to the punchline…
Jumping ahead a bit, here’s the dashboard we eventually came up with:


We incorporated some so-called BANs (“Big Ass Numbers”) to show overall performance during the time period in question, and included a trended graph to show relative changes over time.  I threw in a table to satisfy people who love tables, and threw in some carefully curated color to call attention to the time period in question, as well as demarcating the BANs.

The beauty of this dashboard is that everything comes from an existing published data source, so it will always be as up-to-date as possible.  Basically, I set up two parameters (one each for start date and end date), and went from there.  Here is the main calculation I’m using:


The BANs use this as a filter (keeping “During study” only), and the other graphs use it to determine color.  I don’t show the parameter controls in the final dashboard, but these are available in the specific worksheets:

param sheet

By doing this, I decided that this wouldn’t be a tool for end users, but would instead be a tool for analysts.  This is maybe half way to where I generally like to go, but in this case I think an analyst having a say in the matter is important.

I was really happy with how this turned out, as was the CQO.  And now we can turn this sort of request around in a matter of minutes the next time it comes up!

You can see the interactive dashboard here.




Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.