Creating a Deviation Icicle Chart

Actual to Target (or Expected) Values

Comparing actual to target values is a common client request. Organizations regularly set targets or benchmarks and need to visualize progress towards the target. Bullet charts and bar charts with reference lines can be very effective in showing progress towards a target, to compare current year-to-date to a prior year-to-date, or track actual values to expected values.

Choosing a visual display to show both the actual and expected values over time can be challenging. Diverging bar charts can show the difference from the target – how above or below the value is from the target. This is a good solution when the difference from the target is the most important analysis.

However, when the actual values of the target and current values are needed, then a dual line chart can be used plotting both values. While useful, the biggest con is that the crossing of the lines can quickly become confusing and it’s difficult to tell quickly where values were over/under target and by how much.

Diverging Chart Solutions

In healthcare, the term “observed vs. expected” is a common phrase. This can mean the observed surgery time compared to the expected (or scheduled) time, the actual length of stay compared to the expected length of stay in a hospital, or the expected patients scheduled compared to the patients that came to treatment.

Recently, I worked with a client who needed to compare actual and expected average length of stay (ALOS) for hospital patients. The typical chart may have two lines and look like this (actual in teal, expected in gray):

Screen Shot 2019-12-26 at 4.14.15 PM.png

What I dislike about these dual line charts is that it’s hard to quickly see what months were above or below the target and by how much. I can’t see quickly which months were above or below (or how many months were above, for example) or even by how much without using a tooltip or doing math in my head.

You may think that a diverging bar chart may be a good solution. You are correct that a diverging chart (either a bar or a line) can show the difference between the actual and expected values, but it cannot show the absolute value.

Screen Shot 2019-12-26 at 7.21.09 PM.png

This works really well if the goal is to show how much the actual is from the expected or target. But for a hospital, the actual average length of stay can vary by month as can the expected ALOS because patient population varies each month and there is often seasonality in hospital visits. Certain criteria are used to estimate a patient’s length of stay, resulting in a variable expected ALOS. So while the difference matters, it also matters what those values were. A high ALOS wasn’t necessarily bad if it was still below the expected.

Diverging Icicle Chart

An icicle chart is an effective chart to show both the values of the target and actual values as well as the difference from the actual to the target.

Screen Shot 2019-12-26 at 4.12.20 PM

What I love about these charts is that you can plot the expected values (gray line) and then how far above (red) or below (gray) the actual value was from that expected value. Visually, I can see that the months of May, August, and December had a higher than expected ALOS and by a larger margin than other months. September, on the other hand, was above the expected ALOS, but not by much, evidence by the short red line (or icicle) above the gray line. I can also see that June had a high ALOS compared to months like April, July, and September, but June’s expected ALOS was high and the actual was actually lower than expected. All of these insights are easily obtained from an icicle chart.

Building the Icicle Chart

The icicle chart uses a dual axis line chart. One line is the expected value connected by the time period and the icicles are created using both the expected and actual values and connecting the line by the measure name.

Step 1: Plot the expected value

Screen Shot 2019-12-26 at 7.40.37 PM.png

Step 2: Plot both the Actual and Expected Value

Drag out measure values and just leave the two values you need by removing any others.

Screen Shot 2019-12-26 at 7.43.41 PM

Step 3: Change the second mark type to line (if it’s not already) and move Measure Names from the detail to the Path shelf

Next, select dual axis and synchronize the axes.

Screen Shot 2019-12-26 at 7.43.55 PM

Step 4: Create a calculation for the color (above or below) and add that to the color shelf

You can also add the circle marks to the measure values lines to make the values more pronounced and label only the actual values.

Screen Shot 2019-12-26 at 7.45.10 PM

Step 5: Add labels if desired to the actual values

Screen Shot 2019-12-26 at 4.12.20 PM

And there you have it!

The full viz, with all the other deviation examples can be found here.

Actual to Target

As always, reach out if you have questions or download the workbook to recreate all of these “actual to target” graph options.



One comment

Leave a Reply

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

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

Google photo

You are commenting using your Google 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