September 12, 2018
Overview
I’ve recently seen some questionable approaches to visualizing check-all-that-apply questions (CATA questions) in Tableau. I’m concerned because many of these approaches won’t work if either you filter the data to only show some options, or if some of your survey participants skip the CATA questions.
It’s the latter reason that really gets me as I rarely see a case where everyone who participates in a survey answers every question. If you count everyone that participated in a survey, and not just the folks that answered the CATA questions, your calculations will be off (and they could be off by A LOT.)
In this blog post I’ll show a simple technique for displaying CATA percentages as well as how to make a minor modification so the technique will work with weighted survey data, too.
Getting your data setup correctly
Before going any further, you’ll need to make sure your data is tall and not wide. That is, instead of the data looking like this…
Figure 1 — Wide survey data. Check-all-that-apply questions are highlighted in yellow.
… the data should look like this:
Figure 2 — A snippet of the data showing some responses for Resp ID 2. Note that we can see that this respondent selected “Yes” (1) for Q2_9, Q2_3 and Q2_5.
If this is all new to you, please see Getting Survey Data “Just So.”
I also want to make sure the data is coded as 1s and 0s (1=Yes, 0=No, Blank=Didn’t answer). If your data is not setup this way, make sure to check out Dealing with Survey Tools that Don’t Code Check-all-that-apply Questions Correctly or Using Tableau Prep to Fix Problems with Check-All-That-Apply Questions.
Creating the Check-all-that-apply percentage calculation
Consider the screenshot below where we have a filter that allows us to only focus on the CATA questions in our survey data. I’ve added Question ID and Wording to the Rows shelf.
Figure 3 — Just looking at the group of related CATA questions.
Now we just need to fashion a calculation that will show us the percentage of people that selected one of the nine items; that is, we need to know how many people have a “1” associated with Q2_1, Q2_2, etc.
Here’s the calculation that will do the trick for us.
Figure 4 — How to determine the percentage of people that selected an option.
A quick translation into English would be:
Add up everybody that answered “1” (meaning they selected an option) and divide by the number of people that answered the question.
Let’s see if this works. We can change the default numeric format to percentage, drag the field onto columns, and then sort in descending order.
Figure 5 — Completed check-all-that-apply arrangement in Tableau.
Well, just look at that! We didn’t need COUNTD(), table calcs, LoD expressions, etc., and we can filter this to our hearts content and everything will work perfectly.
Also, if we want to also know the number of responses, we can use this calculation…
Figure 6 — String calculation to show count of people that selected an item and the total number of people that responded to the question group.
… and get something that looks a bit fancier.
Figure 7 — Percentage check-all-that-apply with response count.
But “weight”… there’s more!
In addition to this being a really easy calculation that will work when you filter, add additional dimensions, etc., we can modify it so that it works great with weighted data.
And just what do I mean by “weighted” data? We use weighting to adjust the results of a study so that the results better reflect what is known about the population. For example, if the subscribers to your magazine are 60% female but the people that take your survey are only 45% female you should weigh the responses from females more heavily than males.
You may recall that our data has an additional weighting variable in it called Q0_Weight (see Figures 1 and 2). Here’s what the weighted percentage check-all-that-apply calculated field looks like.
Figure 8 — Percent check-all-that-apply for weighted data.
Conclusion
If your data is setup correctly, it’s easy to visualize check-all-that-apply questions with a simple calculated field that will work with both regular and weighted data.
Related posts
Dealing with Survey Tools that Don’t Code Check-all-that-apply Questions Correctly
