Lollipops for Healthcare Quality Improvement

The Superstore Sales data that ships with Tableau makes life seem so easy: Numbers like Profit and Sales that go up, up, and up. But we don’t only measure things by how high they go, but also how low. Working with hospital quality data, we have small numbers like infection rates that we want to get even smaller. We have rates like the percentage of patients who receive aspirin on arrival that we want to get larger. And then there are measures like how many medication error events are reported that we of course want to get smaller, but we wouldn’t trust if that number got too small too soon because it might indicate under-reporting. Tack on situations like hospital units that may go for months or years without having any events (but are still mandated to report), data arriving at different times, and a variety of units with differing numbers of decimal places, and putting everything on one dashboard gets a wee bit complicated.

This is a process post about how I solved this problem for a hospital quality dashboard with some brilliant help, a dose of calculated fields, and a number of iterations. I presented this dashboard at the Tableau Customer Conference last month, and offer up this post as a a contribution to Tableau Design Month.

Here’s where I was with the dashboard back in April…the layout is definitely influenced by some of the mockups in the first edition of  Stephen Few‘s Information Dashboard Design. The dashboard is for nursing unit leaders and executives, and meant to quickly show performance on a variety of different metrics. One factor in working with clinicians is that they are incredibly focused on their patients. In that brief amount of time that they are looking away from patients to review their metrics, they need information they can take immediate action on. In this case, I was thinking about the nurse manager who has five or ten minutes to review data before the phone rings with a new issue demanding attention.
Screen Shot 2013-10-24 at 11.10.28 PM

Unfortunately, this dashboard wasn’t meeting my requirements:

  • The red circles next to the Fall and Hand Hygiene Rates are a signal to pay extra attention to the metric – they indicate that the metric is below target and is a high priority for the organization – but were often missed by users.
  • And, even more, the bar charts in the middle – that high visibility, “get the most attention here” – space were hard to read. Metrics like the Falls rates at the top are “lower is better” while others like the Central Line Insertion Bundle are “higher is better”, so users would have to mentally switch between looking for shorter and longer bars.
  • A metric with a target of 0 like CLABSI per 1000 Device Days (Central Line Associated Bloodstream Infections, a preventable hospital-acquired condition) ends up using minimal pixels, so if we’re at 0 pixels then that’s “high” performance for the measure. Conversely, a metric like Hand Hygiene might have a lot of data-ink for an underperforming measure and the the combination of two is confusing when our general expectation is that bigger bars are better. Even though I did some tricks with the calculated fields to scale the bar sizes to fit the appropriate range for each metric, this was still a problem.
  • Using reference lines to indicate targets gets confusing for lower is better measures because Tableau’s reference lines are drawn on top of the already-too-small marks. This can be solved using reference bands, but there are minimal sizes for reference bands that are bigger than what I wanted.
  • And finally, the up and down arrows required explanation and a different orientation – they indicate change from the most recent prior metric results, and are oriented so that up is good and down is bad, regardless of the “lower is better”/”higher is better”

I kept on thinking I wanted something like a bullet graph, but couldn’t wrap my head around how to communicate performance compared to target. So I sent out a few emails asking for help, and got responses from Anya A’Hearn – winner of the 2012 Tableau Iron Viz Challenge – and Stephen McDaniel – co-author of Rapid Graphs with Tableau. That lead to a bunch of emails back and forth and one breakthrough screensharing session.

Here was one of the first drafts from Anya. It uses a different color scheme that matches SMMC’s corporate colors (and is easier to read for color blind folks than the grey/green that I’d started with), uses the stick of the lollipop to show where the last period was, and the circle to show current performance, and sets up bands that have the target area in grey, a blue line marking the target, and red line marking the end of the range. The lollipop stick and circle were both color-encoded for whether performance was meeting target:

working-on-it-2 copy

I liked the lollipops and their handles, but was stuck on three things: The target range was still really small for a number of metrics, reading the view still required orientation switches from left to right, and the lollipops were too small. This helped me realize that I really wanted this part of the dashboard to do the following:

  • flag low performing measures (as compared to target) that have a high organizational priority
  • flag low performing measures
  • flag measures that are in target range
  • show comparison to prior period

That’s when I had the webex with Stephen McDaniel. He offered up a key insight, that he likes to orient such views around a single axis, such as “up is good/down is bad”. I’d been doing that already with the up and down arrows, Stephen helped me see that it could work for the main part of the view. Another suggestion was to use a parity line/target line/midline, where the bars could indicate a “distance to target” instead of trying scale to a 0 to N range. I was already scaling the data in my original workbook, so scaling everything to a standardized axis range (like -100 to +100) was conceivable. Here’s the hand-drawn draft that came out of that webex:

photo

So the next steps were to put together a proof of concept in Tableau of how these might look for a range of data. Here was my first attempt, where I was still stuck on using bars:

Screen Shot 2013-10-24 at 11.33.40 PM

I realized as I looked at these bars that I didn’t really care for having big bars, that I was more interested in where the end of the bar was. So I went back to that lollipop idea of Anya’s, and brought in Andy Cotgreave’s work on lollipop charts for the Data Studio (now part of Interworks) to make the ‘lolly’ bigger:

Screen Shot 2013-10-24 at 11.34.29 PM

This was getting somewhere, though I was still color-encoding the lollipop sticks with whether or not the prior performance was in target range, and similar to the realization about the end of the bar being the important bit, I figured out that the end of the stick communicated enough.

I looked at putting labels on the lollipops, but that got too messy:

Screen Shot 2013-10-24 at 11.35.14 PM

I had a few more iterations to go through. Stephen and I had talked about using a dual-encoding of shape and color to help things stand out, so I tried a Consumer Reports-flavored view:

PastedGraphic-1

And one with empty circles:

PastedGraphic-2

Anya kindly sent in this one with filled shapes:

loli-vs-diamond copy 2

After these iterations, I realized that the extra encoding of shape wasn’t necessary, there were few enough categories of values indicated by color and position that we didn’t need any more than that. So here’s where we ended up:

Screen Shot 2013-10-24 at 11.38.52 PM

  • The blue midline marks the target. To the right is meeting the target, to the left is underperforming.
  • Flag low performing measures (as compared to target) that have a high organizational priority with red lollipop circle.
  • Flag low performing measures with light red/pink circle.
  • Flag measures that are in target range with blue circle.
  • Show comparison to prior period (usually a month, sometimes a quarter) with the lollipop stick that runs from the prior period’s performance to the current circle. If there is no stick, then performance hasn’t really changed.

And here’s the dashboard:

Screen Shot 2013-10-24 at 11.40.35 PM

Users can now scan down, find the red, and do something about it. They don’t have to remember which measures have higher is better, or what the target ranges are, etc. The dashboard gives them several options for action via the tooltips:

2013-10-25 12_08_24-

  • Email or call the quality staff member who is responsible for maintaining the measure and supporting quality improvement efforts around that metric. This is great for cases of data denial – “Those numbers can’t be right?!?” and data helplessness – “What can I do???”
  • Review the process control charts for the measure to see common cause vs. special cause. I really would have liked to put the control charts front and center, and as of this writing our organization isn’t quite there yet – folks still like to see comparison to target. (Earlier I’d posted on how to build the control charts yourself.)
  • Open one of several other charts that show more details on the particular measure, Year over Year performance, and/or performance compared to other units. Some of these get printed and posted in the units.
  • Review and edit the nursing unit’s process improvement plan. This is a Microsoft Word document on a shared drive that is accessed via a URL Action.

We’ve got some ideas for how we can make the dashboard even more actionable, that next iteration will be come after we’ve finished incorporating several dozen more metrics into the dashboard and simplified some of the ones that are already there.

How to Build the Lollipop Chart

In the spirit of helping others build this kind of view (and documenting what I’ve done), here’s how to build your own lollipops. I’ll go through what it looks like in Tableau, what the data is like, and how to get the data to create the view.

The Tableau View

In Tableau, the lollipop chart is a synchronized dual-axis chart with axes from -100 to 100, using a Circle mark for the lolly and a Line mark for the stick. The center line is a reference band, and order of the lollipops is determined by a hidden set of header dimensions:

Screen Shot 2013-10-25 at 12.05.32 AM

The Data

The data source for the workbook is a Tableau data extract. Behind that is a set of linked Microsoft Access databases (we’re a non-profit hospital with a small budget) set up in a snowflake schema. The Tableau data connection is to a complicated Access query that pulls information from a variety of tables and subqueries. The reason why I do this rather than a Multiple Tables or Custom SQL connection is for the following reasons:

  • We have to use some subqueries anyways, so why not build the whole query in Access.
  • As many faults as I can find in Microsoft Access, building queries in it is easier than writing raw SQL in the Custom SQL window.
  • I prefer to keep the number of places where I’m manipulating/rearranging/aggregating data to a minimum, so between Tableau calculated fields, the data connection, and the underlying query, I make the data connection simple and move the manipulations to Tableau and the query.

In the Access database, each metric (or measure) has the following static information, stored in a dimension table (there’s a lot more, this is just what’s necessary for the lollipops):

  • Measure Name – uniquely names the metric
  • Measure Dashboard Header – for layout
  • Calculation Type – how the measure will be computed, some are counts, some are rates, along with the unit type (% or number)
  • Decimal Places – # of decimal places to display
  • HigherRateIsBetter – boolean that indicates whether the target is higher or lower
  • Target Rate – this is actually somewhat dynamic as it changes once per year or sometimes more frequently, going into all the details is beyond the scope of this post
  • Target Worst Limit – determines the scaling of the metric to the lollipop chart, roughly equal to the worst performance in the last few years modified by experimentation to find out what creates some visible movement when there is change in the values
  • Stoplight Type – This is used to indicate which metrics are high priority and can actually get a full red stoplight when below target.

And each metric has the following dynamic information, stored in a fact table with a row for each distinct measure result:

  • Measure Date – when the metric was recorded
  • Hospital Unit – what floor or department the measure is for
  • Date Aggregation (aka Window) – we pre-aggregate the metrics at monthly, rolling quarterly, and rolling annual time periods
  • Latest Measure Flag – this is generated by the query that is used to build the Tableau data extract, it’s useful for simplifying computations in Tableau and improving performance
  • Numerator, Denominator, and Rate – these are the key bits of what is displayed

The Data in Tableau

Besides the filters for a specific Hospital Unit or Group of measures, and the date aggregation, there’s one more filter in the Lollipop worksheet:Screen Shot 2013-10-25 at 12.17.14 AM

 

The Latest 2 Measure Dates Flag is a calculated dimension that takes the Latest Measure Flag and does some magic with DATEADD() and DATETRUNC() to grab the prior period’s measure information.

This leads to there being two rows in the view for most every metric – the current month and prior month, and that gives us enough detail to build the view.

Calculations for the Stick

The lollipop stick is make of Line Marks, there are two calculations (I’ve simplified the aggregations here). The first linearly scales the metric to a range based on the Target Worst Limit at -100 and the Target Rate at 0. Since a given result could be below -100 (and therefore off the end of the chart), a second calculation takes anything -94 or lower and adjusts it to -94 to keep it on the chart, but slightly cut off to look like it’s heading off the charts – here you can see the lolly getting cut off:

Screen Shot 2013-10-25 at 12.40.46 AM

//Lollipop Line Feeder
-([Target Rate]-[Rate])/ ([Target Rate]-[Target Worst Limit Value]) * 100
//Lollipop Line
//adjust to make sure that any values that would generate a result off the chart
//stay on the chart at the left edge. sacrificing accuracy to preserve at least
//a bit of information.
IF [Lollipop Line Feeder] <= -94 THEN
   -94
ELSE
   [Lollipop Line Feeder]
END

On the Marks Card for the Lollipop Line measure is the Measure Date on the Path Shelf:

Screen Shot 2013-10-25 at 12.42.26 AMIn my view there are a bunch of other measures and dimensions in the view to generate the tooltips.

Calculations for the Lolly

Since there should be only one circle for the lollipop, Measure Date won’t be on the level of detail. But the view has filtered for two measure dates, so there’s a row level calculation to get the latest rate that looks something like this:

//Rate (Latest)
MIN(IF [Latest Measure Flag] THEN [Rate] END)

Then there’s a feeder calculation followed by the actual lolly calc, similar to what was done for the line so the marks won’t get drawn to the left edge:

//Lollipop Circle (Latest) Feeder
-([Target Rate])-[Rate (Latest)])/([Target Rate]-[Target Worst Limit Value]) * 100
//Lollipop Circle (Latest)
IF [Lollipop Circle (Latest) Feeder] < -94 THEN 
    -94 
ELSE 
    [Lollipop Circle (Latest) Feeder] 
END

And to appropriately color the lolly, the calculation has to do a comparison against target, and this is where we put some intelligence into the calc for higher or lower is better:

IF MIN([HigherRateIsBetter Flag]) == 1 THEN
    IF [Calc Rate (Latest)] >= MIN([Target Rate]) THEN
        "Met Target"
    ELSE
        CASE MIN([Stoplight Type ID])
            WHEN 1 THEN "Did not meet Target"
            WHEN 2 THEN "Did not meet Target and High Priority"
        END
    END
ELSE
    IF [Calc Rate (Latest)] <= MIN([Target Rate]) THEN
        "Met Target"
    ELSE
        CASE MIN([Stoplight Type ID])
            WHEN 1 THEN "Did not meet Target"
            WHEN 2 THEN "Did not meet Target and High Priority"
        END
    END
END

 

The Midline Target

Tableau draws reference lines on top of marks, and I wanted the midline target to be in the background. So I used a reference band, which is set up to be wide enough in the view to have a presence:Screen Shot 2013-10-25 at 1.10.13 AM

Adding the Target Range

Some measures have a range of target values, such as Falls with Injury where the Maine state target is 0.7 falls per 1000 inpatient days – anything between 0.7 and 0 is in the target range. Those are shown in the lollipop chart and the sparkline portion of the dashboard via reference bands that use calculated fields that return the target values when the Show Targets parameter is True, and Null when the target value is False:

Screen Shot 2013-10-25 at 1.18.32 AM

 

To set up the lollipop band, here are the calculations:

//Lollipop Target Feeder
IF ([Calculation Type ID] == 2 AND [Target Rate] == 1)
  OR ([Target Rate] == 0) THEN
  //% measures with target of 100% or measures with a target of 0
    1.5
ELSEIF [Calculation Type ID] == 2 AND [Target Worst Limit Value] == 0 THEN
    (1-[Target Rate])/([Target Rate] - [Target Worst Limit Value]) * 100
ELSE
    -[Target Rate]/([Target Rate] - [Target Worst Limit Value]) * 100
END
//Lollipop Target
IF [Show Targets] THEN MIN([Lollipop Target Feeder]) END

And here’s the reference band:

Screen Shot 2013-10-25 at 1.23.49 AM

Conclusion

Thanks to Anya A’Hearn and Stephen McDaniels for their help, and everyone else I’ve learned Tableau tricks from. If you’d like to check this out yourself, here’s a sample data file and Tableau packaged workbook:

I hope this is helpful to you, it’s been fun to write about and share with people. If you have any questions, comments, or criticisms, please let me know!

23 thoughts on “Lollipops for Healthcare Quality Improvement

  1. Phil Simon

    It’s great to see the evolution of a dataviz. Rarely is the process simple and linear. At least that’s what I discovered researching the topic for the new book.

    Reply
    1. Jonathan Drummey Post author

      I’m glad you liked it! I think the piece that helps simplify the process the most is clarity around what people will do with what they learn, in the beginning of this process I was relatively more focused on the data to be displayed and not enough on what exactly the users needed to see to direct their next actions.

      Reply
  2. Stacey Rothchild

    This is great! As a new Tableau user in hospital metric reporting as well (and geographically close – I will be contacting you for brainstorming!) this is a great presentation of goals and metrics that vary greatly in actual number and scope.

    Reply
    1. Jonathan Drummey Post author

      Please do! I went to college just down the hill (and up another one) from UMassMemorial, at WPI.

      Reply
  3. Stacey Rothchild

    In looking closer at the Excel sheet – is that the result of the query you run out of Access? We are literally starting from square 1 building a database/datamart and I wasn’t thinking Excel was the best way to go to house the data. Feel free to email me directly and I can give you more detail. Thank you for the inspiration!

    Reply
  4. Pingback: Selected Tableau Readings after TCC13 | Data Visualization

  5. Marlan

    Hi Jonathan, love what you did on this! I see that you used a table of with numerator and denominator values to populate the dashboard. I’d really like to use that approach too but can’t figure how to format the results differently depending on the measure. I.e., some are percents, some have $ signs in front of the number, etc. Any suggestions for how to approach this? Thanks!

    Reply
    1. Jonathan Drummey Post author

      Hi Marlan, there are two ways to get multiple formats. One is to format all your numbers as a strings, the second is to use multiple pills on the Text/Label Shelf and use Tableau’s number formatting where each pill is only set up to display for a given format and return Null at other times.

      Here’s a quick primer on how to set up the latter.

      1) The calculated field for one of the measure formats might be something like IF [FormatField] = ‘%’ THEN [MeasureValue] ELSE Null END, another would be IF [FormatField] = ‘$’ THEN [MeasureValue] ELSE Null END, and so on.. Where I’ve done this I have a number format and # of decimal places fields in my data source, if you don’t then you could do something based on the measure name.

      2) Then for each measure format calculated field you’d set the default number formatting.

      3) Put all those pills on the Text/Label Shelf.

      4) Edit the Text/Label Shelf and delete the newlines so all the field references are side-by-side.

      Jonathan

      Reply
  6. Echo

    Hi Jonathan, I am so glad that I came across your post! I work for NHS Scotland and just started building a dashboard with about 20 metrics focusing on nursing care. I would love to use the lollipop charts for my metrics. However, there are some metrics that do not have a target. For example, in addition to hospital falls rate per 1,000 bed days, they also want to know the number of days between the falls. Did you come across similar issue when you were building your dashboard? How would these metrics fit nicely with the lollipop charts? Any suggestions? Thanks!

    Reply
    1. Jonathan Drummey Post author

      Hi,

      Unfortunately, the lollipops don’t work without some sort of target or benchmark because the x axis is centered on the target/benchmark. For some measures that didn’t have established targets we ended up computing a 1, 2, or 3-year average outside of Tableau and using that as the target/benchmark. The number of years chosen depended on how volatile the metric was.

      I strongly recommend computing the number of days between falls outside of Tableau. The reason why is that kind of inter-record comparison in Tableau can only be done with table calculations that require all the fall records to be available to the view and that leads to more complications including requiring more skill & attention to detail in building out the calculations, performance implications, and more maintenance difficulties.

      Jonathan

      Reply
      1. Echo

        Thanks very much for your prompt response. Yes I had a go with calculating the days between in Tableau and it is a pain. I will definitely follow your advice to compute the number outside of Tableau. I will also talk to my colleagues about using some sort of average as the target for those without one. Cannot wait to build my own lollipop chart!

        Reply
      2. Jie

        Hi Jonathan,

        I have been looking at your workbook and Excel data source and have got a question about the field ‘Latest Measure Date for Unit’. You said it was returned by an Access query in your data. I am trying to follow the same logic to compute the field either in SPSS or in Tableau if possible. I undertand how it works when Window is selected to be ‘Monthly’, however, I don’t quite see how it is derived when Window is ‘quarterly’ or ‘annual’. Could you please tell me how it is derived?

        Many thanks
        Jie

        Reply
        1. Jonathan Drummey Post author

          Hi Jie,

          The data source for this dashboard was built by having three copies of the data, one for each “window” or date level (month, quarter, year) and the Latest Measure Date fields were appropriately re-calculated for each level. Then on the main Dashboard view the Choose Rolling Period parameter drives the Chosen Yearly or Quarterly calculation that is on the Filters Shelf. The need to have three copies was because Tableau couldn’t re-aggregate in the way that I needed it to back when I built the dashboard using version 7. In version 9.0 Tableau added level of detail (LOD) expressions, so if you wanted to do this in a single source using LODs it is possible.

          Does that answer your question?

          Jonathan

          Reply
          1. Jie

            Thanks very much Jonathan. I can understand why you aggregated your data to quarterly and annual level.

            So my data comes in as monthly extract and I have used the LOD for calculating the following two fields:

            Latest Measure Date for Measure: {fixed [Indicator New]:MAX([MOY])}

            Latest Measure Date for Ward: ({fixed [Ward Name],[Indicator New]:MAX([MOY])})

            However, I don’t know how to calculate the same for Quarterly/Yearly though. Could you please specify?

      3. Echo

        Hi Jonathan,

        I have managed to make the lollipop chart thanks to your help. I now would like to offer drill-down options for each of the measures, similar to what you have done. My idea is to have another Dashboard called ‘Drill Down’. When I click on one the lollies, it will take me to the Drill Down dashboard showing details of that specific measure. So I have used the Dashboard action and used the specified the field that I would like the master dashboard to filter on: measure name. Is that what you have done in your dashboard?

        The filter actions worked – when I click on Falls Rate, it take me to the Drill Down dashboard and shows me details of the falls. However, I am struggling to make the Drill Down dashboard look attractive (positions, chart title, etc) when the sheets are swapped. Did you come across this issue? How did you solve it?

        Many thanks
        Echo

        Reply
        1. Jonathan Drummey Post author

          Hi Echo,

          Each swapped out sheet takes up 4 pixels of space, so in my own drill down sheet (see https://www.dropbox.com/s/k5gcdu8tlfyv9aw/UnitDashboard%2020130728b.twbx?dl=0 for a blinded version of final dashboard including a drill down sheet) I did a few things:

          1) Set up higher-level categories of measure types so instead of having a separate drill/details sheet for each measure there were sheets for lines, bars, and combo lines & bars, thereby reducing the number of sheets.
          2) Used a separate worksheet for titles, that way it wouldn’t be moving around.
          3) Used separate worksheets for axis titles so I could have control over them. These also got swapped out.
          4) Considered whether I wanted the swapping to be on a horizontal axis or vertical axis. In the case of the charts I felt like having those swap vertically was better because it was less noticeable than a horizontal swap. I also set up the most common chart types to be in the “ideal” location.

          Since then a newer technique has been developed that can get you exact placement, see https://www.interworks.com/blog/rrouse/2015/07/07/how-create-dynamic-tableau-dashboard-layouts-sliding-containers. Also see https://community.tableau.com/docs/DOC-5344 for a lot of tips on sheet swapping.

          Jonathan

          Reply
          1. Echo

            Thanks very much Jonathan for your prompt reply. I will go through your workbook and also follow the links to wrap my head around. Might get back to you with more queries!

  7. emmatrucks

    Jonathan,

    I’ve just come across your blog (was recommended from a recent Boston area Tableau user group). I have been struggling with creating the visualization I want for a new project regarding opioid prescriptions. The team I am working with is insistent that we calculate a 95% CI for our data and also want to be able to see p values. I also am trying to view dates along the x axis in terms of how many weeks before and after have passed since the launch of an intervention. I a, also trying to compare one particular service to all other services in the hospital. I have a draft of the visualization, but can’t get there in tableau. I’ve been using tableau for about 8 months and am still a beginner. Would you be willing to connect via email to offer some advice? I can share the photos and screen shots of the current workbook.

    Thank you,
    Emma Trucks

    Reply

Please add your thoughts and perspectives