January 3, 2018
Note: I first wrote about this five years ago and while the approaches I suggested then do in fact work, the advent of Level of Detail (LoD) expressions in Tableau gives us a much better way to get the job done. A very big “thank you” to my friend and colleague Jonathan Drummey who steered me very quickly to the flexible approach I write about below.
Overview
Those that have followed this blog know that when I setup survey data for analysis in Tableau I separate the so-called “demographic” questions (e.g., gender, ethnicity, education, political leanings, etc.) from the “what you want to know questions” (e.g., “would you recommend this company to a friend or colleague?”, “which of these things do you look for when considering an insurance carrier?”, etc.) The demographic questions remain as separate columns and the other questions get reshaped. So, you may start with 200 columns and 800 rows, with one row for each respondent, and you end up with 20 columns and tens of thousands of rows, with a separate row for each non-demographic question a respondent answered.
This is a solid, proven strategy, but suppose you want to filter / break down a survey question not by a demographic question, but by another “what you want to know” question? That is, suppose you want to see how folks that selected “Yes” to the question “Do you plan to vote in the upcoming election” responded to a Net Promoter Score question?

Figure 1 — A reshaped question acting as s “demographic” filter.
In this pair of blog posts we’ll show you how any reshaped question can be “promoted” to behave like a so-called “demographic” question. That is, we will come up with a flexible, parameter-based approach that will allow any reshaped question to become a Tableau dimension that acts as if it were in its own column from the get-go.
A few thoughts before we plow ahead.
- I’ll be using the same data set I’ve used for most of the examples I’ve blogged about and I will have prepped the data as described here.
- If you know which non-demographic questions warrant this treatment ahead of time you can certainly just copy them and make them separate columns before reshaping / pivoting, thus avoiding the techniques explained below.
- You can also join a reshaped data source to itself but his will produce an overabundance of rows.
- You can join the pivoted data to the unpivoted data and have tens of thousands of rows and hundreds of columns (but you and your audience will be miserable, and performance will be very slow).
A look inside Jonathan Drummey’s thought process
As I was working with Jonathan he jotted down his thoughts in the spreadsheet that I show below.

Figure 2 — How Jonathan Drummey approached the problem.
Pay particular attention to his thoughts expressed in rows 9 through 18. Here’s a flowchart that Jonathan created that goes into more detail (in a slightly different order) about this process of identifying what kind of calculation is necessary:

Figure 3 — Jonathan’s flowchart for determining whether a Level-of-Detail expression or a Table Calculation is warranted.
Starting off easy: understanding our data
Before we come up with an extensible solution let’s start by turning a single question–in this case “Do you plan to vote in the upcoming election” (Question ID = Q0)–and make it behave like a dimension. Here’s a mapping of all the Question IDs, how they group, and the universe of responses to each question.

Figure 4 — Question groups, IDs, wording, and universe of possible numeric and text responses
Now that we’ve examined all of our questions, let’s take look at the respondents who completed the survey and get a sense of who they are.

Figure 5 — The demographics for each survey respondent.
Notice that we have separate columns for Gender, Generation, and Location.
So, how can we promote the “Do you plan to vote” question (Q0) from being a collection of rows into being its own dimension / column?
Plan to vote as a separate column
The LoD expression that will do what we want is this:
{FIXED [Resp ID]: MAX(IF [Question ID]="Q0" then [Labels] END)}
The way to interpret this is
Starting at the innermost part of the calculation, in the IF statement check each record and return the [Labels] value (i.e. “Yes”, “No”, or “Don’t Know” only if the [Question ID] is Q0, otherwise return a Null. Then, for each [Resp ID] in the data, return the maximum value of the results of the IF statements for that respondent, where MAX() will return “Yes”, “No”, “Don’t Know” or NULL if the respondent had never answered that question.
If you are wondering why you need the MAX() function it’s because you need to have some type of aggregation when using an LoD expression. Note that MAX() or MIN() will both work as they will accept text as an argument, while SUM() and AVG() will not work.
You can now drag this newly-created field, named [Plan to vote] and use it as you would use any dimension, as shown below.

Figure 6 — “Plan to vote” as a demographic dimension.
Do note that there are some Null values as some respondents did not answer that question. You may want to alias these as “Did not respond”.
Making this flexible
This technique will work for elevating any single question to behave as a dimension and if you only have a handful of question you want to treat this way you need not read on.
But suppose you have dozens of not hundreds of questions that you want to explore as dimensions? You certainly won’t want to “hand chisel” hundreds of separate LoD expressions.
Instead, we should create a parameter-based solution that will allow users to select the question they want to “promote” from a drop-down menu.
You can read how to do this in Part 2.
[…] January 3, 2018 update. I’ve written a two-part post that explores better ways to conduct intra-question analysis. Please see this post. […]
This is great! I work with survey data all the time and I bumped into this exact issue recently. I like this actual solution rather than my made up one, which was to go back and re-shape the data to pull out my question into a column. Thanks as always!
Hi Steve, you did it again. Amazing stuff.. I am learning all over again. Thanks for all you do.
This information is so exciting yet a bit overwhelming!! I discovered tableau as I was searching for a comprehensive and effective way for non-data analysts (aka non-profit teachers with la to share information with teachers. I’m hoping someone out there in the world of data can help me decide if this tool can work for the task.
Here’s the scenario: We have identified 5 dimensions of learning and use a 30 question Likert survey to collect student’s self-report data along these dimensions. For example, we may have 6 questions that collectively give us a score on Students’ perception of their “stick with fitness (just an example). The student data is compiled by classes into excel sheets, looking very similar to those presented here i.e. student demographics and questions in columns.
Reconfiguring the data aside (I understand we will need to do that), would it be possible to create individual student graphs of some type to show how each individual student scores on each of the 5 dimensions? IF so, is that manageable for someone just exploring tableau?
Kim,
It sounds like you are looking to get the average Likert score for the five groups / dimensions. This is not difficult to do. It’s also not hard to show the average score for the various dimensions for each student, or to compare a particular student with his / her peers.
There are several ways you can tackle this:
1) Go to https://www.datarevelations.com/visualizing-survey-data and watch some of the videos and read some of the articles (particularly the post about getting data setup correctly).
2) If you get stuck, you can send me your data and I’ll try to give you a quick jump start.
3) You can go to Tableau website and post your question to the forum (lots of great people helping other people out).
4) I offer an online class on visualizing survey data with Tableau every few months. The next one is February 27 and March 1 (two half days). See https://surveydatafeb.eventbrite.com
Kindest regards,
Steve
Hi Steve, this is really helpful. Please I do have a question. using the MAX() function for the LOD calculation does that mean the calculation only returns that maximum value for that record?
Thanks,
Ije.
Lje,
Are you referring to this:
{FIXED [Resp ID]: MAX(IF [Question ID]=”Q0″ then [Labels] END)}
We’re dealing with text, so it’s not the max possible value for the question; we’re just trying to say “if [Question ID]=”Q0″ then do this thing”, but we can’t just say it that way because Tableau LoD expressions insist there be some type of aggregation. Max and Min will work with text; SUM() will not.
Steve
What if the question I want to cut my data by is a multi-select question? The fixed calculation works in this example because it’s a single select question.
Patty,
Filtering / breaking down by check-all-that-apply questions is a work in progress, but I’ve got some good stuff on it here:
https://www.datarevelations.com/visualizing-survey-data
Look for the video from the TC2018 “What’s New In Survey Data” presentation. I cover this in the second half. The workbook and examples can be downloaded, too.
The other way to tackle this is with set actions, but I think I prefer the method I cover in the video. I will blog about this at some point, but the video should give you what you need.
Steve
Hi steve,
Thanks for the quick response! Able to access your presentation and download the files associated with it, really helpful. Only small problem is I use Tableau 10.5 and it will not let me open your workbooks bc they were created in a newer version of the software. Any chance you can publish them in an older format? Thanks!