As discussed in my last post, Hospital-acquired Infections (HAIs) are a big deal, and hospitals spend a lot of time, effort, and money trying to prevent them (see pt 1).
I worked closely with my hospital’s Chief Quality Officer to develop our initial HAI dashboard. During one of our many meetings iterating dashboard design, he mentioned that what he really wanted to see was how this looked geographically. As an explanation, he drew a picture:
This way of looking at infections is helpful for a couple reasons. First, it helps identify possible epidemics based on physical equipment or shared resources. Second, it helps users identify patterns, quickly comparing different units on a more visceral level.
A first iteration
I did a lot of research on how to do this, and ended up using custom polygons over a background image. You can read about similar approaches in many blogs, including those by Ryan Sleeper and Bryant Howell. I did this before the Power Tools Drawing Tool existed, so I had to use Point Annotations to find the pixel values of each point. While it worked, it was pretty annoying to build.
A second approach
A few months later, our hospital finished construction on a new tower, and I was facing the prospect of having to completely redo all of my tedious pixel mapping work. After a fair amount of procrastination, I came up with an elegant (and much easier) solution.
Because the desired image is in a grid form, I can get what I need by using a Gantt chart in a funny way. If you think of each unit as a mark on a Gantt chart, all you need is a starting point, a width, and a vertical row for each unit. It might help to show my Excel setup:
Here, I’ve highlighted the new Peds Tower. This is the first column in my desired image, so each Gantt chart mark starts at 0. I have rows 1, 3, and 4 defined, matching the picture above. Column width specifies how wide to make the mark (this will become more interesting in a moment).
I then added some more rows for labels and ‘blank’ units (i.e. units that don’t currently report HAIs):
“Unit” can be interpreted as a unique identifier for helper rows (i.e. labels and blank units). Cell coloring is only for ease of reading.
Building it in Tableau
To get this to work properly in Tableau, I had to start with my mapping table (above), and join in my NHSN flat files.
From this point on, the build wasn’t too bad:
One interesting thing worth noting is the “Color – CLABSI” calculation. This is basically the CLABSI infection rate, but there are two things I wanted to catch:
- If a unit has infections with a low denominator, that would skew the data
- Labels and blank units should be handled appropriately
To do both of these, I built a calculation like so:
And specified my color range like so:
I then repeated this build for CAUTI, C diff, and a newly created “Total HAIs” metric. Put it all in a dashboard, and voila!
This has turned out to be a very useful, and very popular, dashboard. Our Hospital Epidemiologist likes being able to see trouble areas in a more visual way. And long-time staff, who intimately know the hospital’s layout, love being able to quickly see infection data in a way that seems instantly familiar.
Plus, now that we have the mapping and the methodology, we can use this technique for any topic!
See the interactive dashboard here.