5 Replies Latest reply on Sep 27, 2019 9:40 AM by Bryan Pon

# Creating chart with multiple dimensions from normalized survey data

I've tried searching the forums, but not quite sure what I'm event trying to ask. My sense is this is a basic conceptual misunderstanding/gap that I have.

I have survey data that is formatted such that each row has a respondent's answer to a question.  We can say the columns are: respondent id, question, response.

 id question response 123 car honda 123 married yes

I can filter the data to easily create views on any one question (e.g., how many respondents have a Honda). But I can't figure out how to combine multiple questions into a single view (e.g., how many respondents with a Honda are married).

I made a simple example workbook, attached. In this example what I'd like to do is show how many of the respondents are married by applying the color mark to the bar chart.

Thanks!

• ###### 1. Re: Creating chart with multiple dimensions from normalized survey data

Hi Bryan,

Use this calculation:

{ FIXED [Id] : max(if [Question] = 'married' and [Response] = 'yes' then 1 END)}

thanks,

Mavis

• ###### 2. Re: Creating chart with multiple dimensions from normalized survey data

I think the main problem is the permutations and combinations of questions that can be asked. One combination is easy enough to solve.

It's a very interesting problem though.

• ###### 3. Re: Creating chart with multiple dimensions from normalized survey data

Survey data poses unique challenges, and personally I wouldn't attempt it without immersing myself in Steve Wexler's Tableau/Survey data treasure trove:

• ###### 4. Re: Creating chart with multiple dimensions from normalized survey data

Thanks all!

Subodh, yes, exactly that--What I'm looking for is a *general approach* that I can use to explore the dataset, rather than crafting a one-off calculation for every combination that I want to explore.

Mavis, I think that works.... I would create calculated fields for each question using LOD expression.   The syntax you used would require a binary answer, so for many questions ("type of car") I would have to reshape the data such that questions with multiple-choice answers are broken down into subquestions that have binary answers....unless you can think of alternative?

Cathy, thanks! I just found those articles and am working through them, obviously spot on relevant. The general data preparation approach he describes (tall, not wide data) is what we've done, though we haven't yet formatted our CATA (check all that apply) responses per his suggestion.  I haven't seen yet an article describing manipulating actual visualizations with survey data, but I'll keep on looking.

• ###### 5. Re: Creating chart with multiple dimensions from normalized survey data

Mavis, your solution works great with a slight modification:

{ FIXED [Id] : max(if [Question] = 'married' and [Response] = 'yes' then 1 END)}

Can simplify to this, which also allows me to continue to use the strings:

{ FIXED [Id] : max(if [Question] = 'married' THEN [Response] END)}

This is actually what is recommended by Steve Wexler, who as Cathy pointed out has a wealth of fantastic information on working with survey data. And I found he has a specific blog post on how to do exactly what I was asking:

Cut and filter by any other question - Part 1

Cut and filter by any other question - Part 2

Steve's articles also suggest a more flexible approach using parameters that I am going to explore.

Thanks all!