Exploring an unusual and powerful visualization technique.
We're all familiar with traditional stacked bar charts.
And you're also likely familiar with offset bar charts, such as those used in waterfalls:
Well, buckle up, because we're going to discuss how to combine these two concepts into a massively powerful visualization tool that you can proudly add to your toolbox!
When Should You Use This?
When the main story is the relative widths of the individual segments between rows, or the data is such that it is not very meaningful to add up the heights of the individual segments to get an aggregated total for the entire row. For example, let's say you are a program manager and are trying to see how many projects you have per department. A project can be in one of several stages: Active, Completed, Canceled, Planning, Idea Phase I, and Idea Phase II. It's not meaningful to compare the total number of projects without regard to the project stage—adding up Completed and Cancelled projects into a single aggregate number, for example, does not make sense. The stage breakdown is, therefore, the key part of each story.
The main advantage of offsetting a standard stacked bar chart in this situation is that you get a common baseline against which you can compare the widths of any given project stage. Meaning, all segments that represent a single project stage will start at the same place, almost as if they have been given their own "sub-axis." This lets you easily perform apples-to-apples comparisons and find outliers among any given bar segment quickly. By contrast, look how much more difficult that type of analysis would be without offsetting:
When Should You Not Use This?
When the main story is the sum of all the individual bar segments. For example, suppose you want to create a chart of sales by business unit. You're mostly interested in the total sales volume for each business unit, but you feel it might enrich the data to break up the bars into segments that represent each of your three major markets. A chart like that would best be represented without offsetting the bar segments, because doing so would obscure your main message: the total sales per business unit.
You should also not offset your stacked bars when the main message you are trying to convey is relative percentage out of 100% (like in a pie chart). For instance, let's say you are trying to plot out survey responses:
The reason you wouldn't want to offset this type of chart is because you would end up losing the intuitive message that each segment is a part of a whole.
So How Do You This?
I can't speak to other BI tools, but it turns out there's actually a decent amount of code involved to bend QlikView to your will in this case. Rather than trying to paste it here, I would encourage you to just download the following app and explore on your own.
I've commented the code liberally, but just want to explicitly call out a few "gotchas" that you should be aware of:
- There are, of course, two dimensions to this chart. The first dimension is used to generate rows, while the second is used to generated the bar segments. You need to make sure that every value of the first dimension has an association in the data model to every value of the second. To see how we do this, take a look at the script.
- In a stacked bar chart, the "offset" value (the amount by which you want to offset each segment) is calculated relative to the end of the last drawn segment. Meaning, if you have one segment that starts at 0 and has a value of 100, and you want your second segment to start at 125, you need to specify an offset of 25, not an offset of 125. This works differently than with grouped bar charts, where the offset is always relative to 0.
- Make sure you uncheck "Suppress Zero-Values" under the chart Presentation settings.
Why Not a Trellis?
An offset stacked bar chart is essentially the same thing as a trellised bar chart. If that's the case, why don't we just use QlikView's native trellis capabilities? To be frank, because they're not very good. If you try to implement this with native trellising, you will likely run into at least three serious limitations:
- You have to print the row's dimension value (e.g Department) on every trellis column, which is a huge waste of real estate that could better be used to draw the bar segments themselves. Not to mention it's just ugly.
- A native trellis charts doesn't intelligently take up available space by rescaling to filtered values of the trellis dimension. For example, if you filter down to only 2 project stages, a native trellis chart will still show them as two narrow columns, instead of dividing the available real estate into two spacious columns. Likewise, the "auto" sizing option is quite unintelligent.
- If you have lots of rows, you should eliminate trellis charts from the running immediately. That's because scrolling along the non-trellised dimension simply does not work.
Thank you for reading, and shoot me any questions you may have (or ideas for improvements!) in the comments section below!
↓↓
↓↓
