Showing Differences between Periods and Statistical Significance in Tableau
Addressing this scenario has been the most popular request I’ve received over the past year. Here’s a summary what my clients and students have asked:
- How do I show the change in Sales, Percentage of Promoters, Number of Visits, etc., between this month / quarter / year, and the previous month / quarter / year?
- How do I make it easy to see which areas of the organization had an increase this period and which had a decrease?
- How do I make it easy to see how much greater / less this period’s numbers are than the previous period?
- How do I determine and show if this change is statistically significant? That is, how do I apply the stat test we like to use in our organization?
- If the change is statistically significant, is it a one-time thing or should I start hyperventilating?
This is a LOT to take on and we won’t be able to fit all of it into a single visualization.
But we can fit it into a compact dashboard.
Important Ground Rules
In the example that follows I look at the percentage of people that responded with a “9” or “10” to a survey question. That is, I am only looking at the percentage of people that selected one of the top two boxes. I am NOT trying to see if there is statistical significance or calculate the margin of error in the change in Net Promoter Score over time.
The concepts I explore are not just for survey data; I just happen to have some good longitudinal survey data that is well-suited for seeing how to build a stat test formula in Tableau.
I hope you will indulge me and accept that “the company stat guru” has a fine reason for applying a particular statistical test to the data we’ll be analyzing. That said, you should push back on “business-as-usual” assumptions to determine if what you are visualizing and testing really is important (this is the focus of the work Stacy Barr is doing with her Measure Up blog and is the foundation for Stephen Few’s most recent book Signal.)
So, with the assumption that the particular stat test we want to apply – or any stat test, for that matter – is warranted, how do you show it and how do you build it?
Let’s first explore the working dashboard then see how to build it with Tableau.
Note: A very heartfelt thanks to Kelly Martin,, Joe Mako, Vicki Reinhard, Susan Ferarri, and Tiffany Spaulding who helped vet the dashboard. I went through many different approaches before settling on the one shown below.
A very special thanks to Jeffrey Shaffer who reviewed the blog post and asked some very good questions, and also to Helen Lindsay who provided sample data.
The data and what we want to show
The data below contains the first few rows of Net Promoter Score survey data with fields for date and role.
Figure 1 — Net Promoter Score survey data with dates and roles
For the dashboard I built I only focused on the percentage of people that were Promoters; that is, people who responded with a 9 or 10 when asked if they would recommend a product or service.
I decided to look at the data broken down by quarters as this particular data set didn’t lend itself to month over month comparison. Note that the techniques we’ll see will work for any time period.
Here’s the top portion of the interactive dashboard.
Figure 2 — Top portion of dashboard. Notice that you can change the selected period, the confidence percentage, and filter by company.
Understanding the chart
Figure 3 — The key features of the chart
Let’s review what we can glean from the chart. We can see
- The percentage of promoters for a particular period and sort them by role, using a bar chart.
- Which roles have a percentage of promoters that is greater than the previous period and which have less, using color to distinguish (blue for greater, brown for less).
- Just how much more or less the percentage for this period is compared to the previous using a reference line (the bar is the current period; the vertical line is the previous period).
- Which roles showed a significantly significant increase or decrease (the red dot).
Note that that the chart uses “Cotgreavian” tooltips that allow you to glean more detail for a particular role when you hover over a bar:
Figure 4 — Hover over a bar for in-depth information about the role for the current period and the previous period
So, we can see from the red dot that something is up with Lawyers, Doctors and Nurses; that is, the percent increase from the previous period for Doctors and Lawyers is statistically significant and the percent decrease for Nurses is also significantly significant. Is this a one-time thing or a trend?
Looking at changes over time
Clicking a role or roles will display trends for that role / roles. For example, if we select Nurse in the top chart a second chart showing percentage of promoters over time will appear, as shown here.
Figure 5 — Percentage of nurses that are promoters, over time.
The big takeaway for me is that up until the first quarter of 2013 there were very few responses and after that there was both a consistent number of responses along with a consist decline in the percentage of nurses that were promoters.
Should you be hyperventilating because of the four-month downward trend? That discussion is beyond this blog post but I again encourage you to check out the work Stacy Barr is doing at her Measure Up blog as well as Stephen Few’s most recent book Signal.
How the This Quarter vs. That Quarter Chart is Built
Let’s dig into how to build this in Tableau, starting with the top viz in the dashboard.
Figure 6 — What’s under the hood.
- Promoters – Current Quarter. This is the measure that drives the bars. It’s also driving what appears on the labels.
- Promoters – Previous Quarter. This measure is on the Level of Detail and drives the reference lines.
- Greater / Less. This is a discrete measure that determines the color of the bar.
Promoters – Current Quarter
What we want is the percentage of people that were promoters for the selected quarter, the “selected” quarter being determined by a parameter that the user can control.
Specially, we want to add up everybody that responded with a 9 or 10 for the selected quarter and divide by the total number of people that responded. Here’s the calculation that handles this.
IF [Value]>=9 and DATETRUNC(‘quarter’, [Select Period])==DATETRUNC(‘quarter’,[Date])
then 1 else 0
IF DATETRUNC(‘quarter’, [Select Period])==DATETRUNC(‘quarter’,[Date])
then 1 else 0
The translation into English is
Take the sum of
If the value from a respondent is greater than or equal to 9 and the date value, truncated to the nearest quarter from the parameter drop down [Select Period] is the same as the date value, truncated to the nearest quarter for [Date], then 1, else 0.
Divide this by the sum of
If the date value, truncated to the nearest quarter for the selected period is the same as the date value, truncated for the nearest quarter for [Date], then 1, else 0.
Not sure about the [DATETRUNC] function vs. the [DATEPART] function? Have a look at Joshua Milligan’s excellent post explaining date values vs. date parts.
Promoters – Previous Quarter
This calculation is very similar to the calculation for the Current Quarter, except we want to find results for the quarter that occurred just prior to the selected quarter. Here’s the calculation.
IF [Value]>=9 and DATETRUNC(‘quarter’, [Select Period])=DATETRUNC(‘quarter’,DATEADD(‘quarter’,1,[Date]))
then 1 else 0
IF DATETRUNC(‘quarter’, [Select Period])==DATETRUNC(‘quarter’,DATEADD(‘quarter’,1,[Date]))
then 1 else 0
The formula is the same except we use the DATEADD function to add an additional quarter; that is, we’re saying that we only want to find results where, when we add an additional quarter, we get a value equal to the current quarter; i.e., the previous quarter, plus one quarter, gives us the current quarter.
Greater / Less
The color of the bars is determined by this discrete measure:
IF [Promoters — Current Quarter] > [Promoters — Previous Quarter] then “Greater than previous”
else “Less than previous”
Yes, I suppose we should have a contingency for when the percentage of promoters for the current period is the same as the previous period; I leave it as an exercise for the reader to add this functionality.
So, we’ve explained everything except … The Red Dot.
The Red Dot – Computing Statistical Significance on the Fly
Most of my clients and students are surprised to find out that you can fashion a test for statistical significance inside Tableau and it can test for statistical significance “on the fly”; e.g., you can apply filters and Tableau will recalculate based on the filter settings.
The first step is determining just how the client wants to test for statistical significance. This usually entails sending an inquiry to “the stats person” who responds with something that looks like this:
Figure 7 — Z-test formula for statistical significance
I hope your eyes aren’t glassing over as this really isn’t very complicated; it just might look complicated if you’re not used to seeing stat formulas with square root symbols. Here are the critical things you need to know:
p1 Percentage of promoters for the current period
p2 Percentage of promoters for the previous period
n1 Number of respondents for the current period
n2 Number of respondents for the previous period
If z1 is greater than or equal to 1.96 then there is a 95% degree of confidence that the difference between the two periods is statistically significant.
So, how do we build this formula?
Slowly, and in easy-to-digest pieces.
The Dot Itself
Figure 8 — The discrete measure Z-Test Significance Dot is responsible for displaying the dot
The calculation that produces the dot is called Z-Test Significance Dot and it is defined as follows.
IF ABS([Promoters — Z-Score Quarter])>=[Confidence] THEN “•”
This translates as
If the absolute value of [Promoters – Z-Score Quarter] is greater than or equal to the confidence parameter (currently set to 1.96, or 95%) then display a dot; otherwise, display a null string.
And just how is [Promoters – Z-Score Quarter] defined? Let’s explore the next layer of the onion.
Promoters – Z-Score Quarter
This is defined as follows:
[Promoters — Z-Score Quarter Numerator] /
([Promoters — Z-Score Quarter Denom – Current] +
[Promoters — Z-Score Quarter Denom – Previous])
Here’s how it maps to the stat formula we saw earlier:
Figure 9 — Mapping the components of the formula to different calculated field
So now we just need to understand the three different pieces that go into the stat function.
Promoters – Z-Score Quarter Numerator
This is very simple and refers to calculations we’ve already used.
[Promoters — Current Quarter] –
[Promoters — Previous Quarter]
Promoters — Z-Score Quarter Denom – Current
This is fairly straightforward given what we’ve already explored.
([Promoters — Current Quarter]*(1-[Promoters — Current Quarter]))
/SUM([Promoters — Current Quarter Count])
Where [Promoters – Current Quarter Count] is defined as follows.
IF DATETRUNC(‘quarter’, [Select Period])==DATETRUNC(‘quarter’,[Date])
THEN 1 END
So SUM(Promoters — Current Quarter Count]) is just adding up all the people that responded during the selected quarter.
Promoters — Z-Score Quarter Denom – Previous
([Promoters — Previous Quarter]*(1-[Promoters — Previous Quarter]))/
SUM([Promoters — Previous Quarter Count])
This uses the same logic as [Promoters – Z-Score Quarter Denom – Current] but instead aggregates results from the previous quarter.
Putting it all together
In addition to building the components in a piecemeal fashion I will often build a crosstab of all these components to see if they are working as I would expect. Consider the crosstab shown here.
Figure 10 — Crosstab showing all the pieces that contribute to the red dot
The cross tab allows us to examine all the intermediate calculations to see how the contribute to the determining calculation in the last column.
What about the secondary chart?
So we’ve now seen how to build the top chart that shows current and previous quarters broken down by role. How does the secondary chart – the chart that appears when you click a role or roles in the first chart – work?
Figure 11 — Percentage of promoters for Nurses over time
Here we have a dual axis chart so that we can have both a line (gray) and a circle (colored based on whether the change for the previous period is statistically significant).
In this case we have to construct all of the pieces using a table calculation, but the process of putting together the different components is identical to what we saw earlier. For example, the calculation that determined the color of the circle, [LONG_Z-Test Significance], is defined as follows.
IF ABS([LONG_Z-Score])>=[Confidence] then “Significant”
else “Not significant”
And [LONG_Z-Score] is defined this way:
([LONG_Z-Score Denom Current] +
[LONG_Z-Score Denom Previous])
I also built a crosstab to see how all the pieces fit together, as shown below.
Figure 12 — Crosstab to help put together a z-test calculation for values shown over time
The dashboard in this blog post shows the percentage of promoters, sorted by role, for a particular quarter, compared with the percentage of promoters for the previous quarter. Roles where the percentage difference is statistically significant are marked with a red dot. You can drill down on a particular role (or role) and see how scores have changed over time.
While the critical visual component was showing bars and reference lines, most of the “heavy lifting” went into determining if a change was statistically significant. The key here was to not be intimidated by a statistical formula and to build the calculations in small pieces, using crosstabs to check the work.