August 6, 2019
Since the release of The Big Book of Dashboards
in the Spring of 2017 I’ve received a lot of requests to explain how to build
the Churn dashboard in Tableau. I’m glad I waited to write the post as my
friend and colleague Klaus Schulte came up
with a considerably better way to both structure the data and craft the waterfall
/ line chart visualization.
I’ll write about one of several ways to build the view in a
moment, but first let me make sure people understand the scenario and how the
data needs to be structured.
Note: You can read the entire Churn dashboard chapter
for free by clicking
Scenario and data set
Let’s say you need to track subscriber churn so that for
each month you know how many people sign up for a service and how many people cancel,
broken down by different divisions. Your source data might look like this in
Figure 1 — Twelve months of churn data in Excel.
Before we can do anything in Tableau we’re going to need to
ditch the wide format and have a single date column. Using whatever tool you
prefer (Alteryx, Easy Morph, Tableau Prep, or Tableau’s built-in pivoting
feature, etc.) we need our source data to look like this.
Figure 2 — Reshaped data. We just need to know how many we gained and how many we lost for each division in each month. We’ll be able to calculate everything else in Tableau.
How the main view works
I experimented with several different GANTT chart approaches
and will explain the technique that I think is easiest to understand. All the GANTT approaches require a little bit
of “move the slider to the right until it looks right” uncertainty versus
something that is “pixel perfect.” Klaus has developed a Polygon approach
that’s “pixel perfect” but it does require more preparation work (and a
lengthier blog post!)
Here’s the combined waterfall / jump line view.
Figure 3 — Waterfall chart with jump line overlaid.
We can see by (1) that we’re dealing with a dual axis chart,
let’s separate this into two views and focus on the Month Offset component
first, which is responsible for drawing the paired GANTT bars.
Understanding the GANTT bars
Figure 4 — What’s behind the GANTT chart. Note that SUM(Amount) is on detail so that the amount gained or lost for a month appears when the user hovers over a bar.
Month Offset (1) is a calculated field and is defined as
IF [Description]="Gained" THEN
This translates as “round the date to the nearest month, if
we’re looking at gains, move the mark a little to the left (minus Offset) if we’re
looking at losses, move the mark a little to the right (plus Offset).”
This is where things get a little wonky. Offset is a parameter and it’s currently set
for 5.3. Why that number? Because
combined with manually sizing the GANTT bar (2), it looks good.
Notice that the field [Description] is on color. This creates two separate marks: gray for
“Gained” and red for “Lost”, for each month.
So, at this point we know that we have a pair of offset
GANTT bars for each month. Where should
the marks start (i.e., how high or low) and how tall should they be?
The field [Running SUM(Amount)] determines where the bars
should start. This field is defined as follows.
This table calculation tells Tableau to add the current
month to the sum of the previous months. It is critical that “compute using” be
setup as follows.
Figure 5 — Make sure that the Description field isn’t first.
Actually, the only critical thing is that the [Description]
field come after one of the date fields.
So, now we know where the GANTT bars need to start. How long
should they be? This is determined by size (5) where we have the field
Why the negative?
That’s how GANTT bars work. You
figure out where the end should be and then extend the marks down from where it
Understanding the jump line chart
Figure 6 — What’s behind the jump line chart.
We start by placing the MONTH([Date]) as a continuous datavalue on columns (1). This will center our line chart (2) at the start of the month versus offsetting. Notice, too, that we have the same computation on the rows shelf, but we do not have a designation for color, so we get a single line chart, colored blue, that shows the overall running sum of net subscribers.
Clicking the Path button will reveal that we’re using a
“Jump” line type (3).
Figure 7 — Specifying a “Jump” line type.
We specify a “Jump” type of line as we just want a
horizontal line connecting the months and don’t need to draw vertical lines.
I think adding mark labels is optional, but if you do want them sure the labels (4) are right aligned along the bottom.
Figure 8 — Label settings.
The last step is to make this a dual axis chart and make
sure the axes are synchronized.
Iterate, iterate, iterate
This single visualization – let along the entire dashboard –
went through a lot of iterations. The version we published in the book looked
Figure 9 — Original version of waterfall / line chart combo.
I recently flirted with a zig-zag chart that made it easy to
go down the path of “this many people signed up, then this many people
cancelled, then this many people signed up”, etc.
Figure 9 — Zig-zag chart. A little on the noisy side.
I think both the original and the zig-zag work, but I prefer the clean look of the jump line. Again, I very much appreciate Klaus showing me that I didn’t need to compute the net results for each month as a separate field in the source data and for coming up with the jump line technique. I think this is a good approach as the data prep is easy, the chart calculations are easy, and we have a clear and easy-to-understand viz.
Want to explore the complete dashboard and download it?
Click here to read Klaus Schulte’s blog post that explores his pixel-perfect polygon technique.