Jun 122013
 

Note: This post is dated but contains useful information. I’ve discovered some better ways to handle this but haven’t blogged about it yet. Feel free to nag me and I’ll either write the post or conduct a screen-sharing session.

Overview

Note: If you have not already done so, please review Part 1, Part 2, and Part 2 ½ of this series.  You can also download the source data from here.  The completed interactive dashboards can be found at the bottom of this post.

I continue to get questions about how to handle survey-related issues in Tableau.  The latest inquiry comes from Tony in the UK where Tony asks how to address respondents that are members of multiple organizations / categories.  Here’s an example of the type of “demographic” question that Tony has in mind:

In what countries does your organization maintain operations (check all that apply):

[ ] USA

[  ] Canada

[  ] Japan

[  ] Liechtenstein

etc.

Our goal is to have a dashboard that either looks like this:

Dashboard using action filters

or like this:

Dashboard using quick filters

Here’s how the country data might be encoded by the survey tool where 1 indicates yes and 0 indicates no.

First Approach — One Table

Depending on your willingness to use visual filters (actions) you can treat these Country fields like all other “Question” fields and reshape the data so that it looks like this:

You can now create a visualization that looks like this one:

There are a number of funky things going on here to make this work properly.  Let’s first look at what is on the filter shelf where we are filtering by Question and Value.

The Question filter is set to show just the so-called “Country” questions, as shown here.

Note that UK and USA are at the bottom of the list.  Also note that for the main visualization in the dashboard (the one that shows “Will you vote in the next selection”) the Question filter is set differently.

We also need to set the Response values to 1 so that we get a proper tally for each country as the survey was set up so that 1 = yes and 0 = no.

Level of Detail

You’ll notice that we have ID on the level of detail.  This is because for the main visualization (the one where we ask the question “Will You Vote In The Next Election”) the Question filter is set differently.  We need this level of detail for our action filter so that we can pass the IDs of folks that are in a particular country to the other visualizations on our dashboard.  Note that normally when you add this level of detail you would see a separate bar for every ID.  We get around this by turning Stack Marks off from the Analysis menu.

We also need to use a table calculation (we’ll call itCheckAll_Count) to sum up all the responses across ID:

TOTAL(SUM(Response))

Does This Work?

If you select the country name (so that all marks are selected) the visual filtering will work fine.

If, however, you select the bar itself you run into problems as you end up passing only one ID the other visualizations.

So, is it okay to go into production with this?  That is the reader’s call, but I can pretty much guarantee that if you show this your CEO he / she will click the bar and not the label, so…

 

Second Approach — Two Tables

The advantage of the next approach where we join two tables is that you won’t run into the possible user experience issues cited above and you will be able to use quick filters.  The only downside is that we end up with many more rows of data (but Tableau can handle it like a champ…Tableau Public, on the other hand, balked at allowing me to upload a workbook with more than 100K rows).

In this example I’ve created a second table that just contains the “Country” questions, reshaped.

We then craft the following join in Tableau.

Note that we could also have joined the main table (Reshaped) to itself to achieve similar results.

We now have Country data reshaped and separate from Question data, and we can filter by Country.

We can also craft a visualization using action filters without fear that somebody will select a bar rather than the entire category.

Summary and What’s Next

We now have a way to handle “OR” logic pretty easily and can handle queries like “show responses from people with operations in China, or Japan, or France”.

But what about “AND” logic where we only want to see responses from people with operations in China AND Japan AND France?

If there’s enough interest I’ll write another post.

[suffusion-the-author]

[suffusion-the-author display='description']
 Posted by on June 12, 2013 2) Visualizing Survey Data, Blog  Add comments

  21 Responses to “Using Tableau to Visualize Survey Data — Part 3: Dealing with Multiple Memberships for Respondents”

Comments (21)
  1. Looks like there’s not enough interest to warrant the follow-up on handling “AND” logic for multiple memberships for respondents (darn). Instead, would you be willing to provide a paragraph or two description here in the comments of how this might be done?

    Thanks again for putting this entire 3.5 part series together, it’s been a big help to me on a recent project involving raw survey data sourced from an online survey tool.

    • Chris, I’m traveling but feel free to nag me about this in a week or two…

      • AND operation in Tableau is something that I have been breaking my head to find answers to. I found the set operation in Tableau which allows the intersect operation. But my issue is to visualize this is an appealing manner. Venn Diagram is probably one of the best visualization for set operations, but becomes very complex when more than 3 sets are involved.

        Can you please elaborate on your methods to approach the “AND” operation and also visualize the intersection of different groups without venn diagram

        • Nanditha,

          I don’t think this is really a survey question issue and is a more generic how do I show overlapping relationships. The stuff I was writing about had to do with looking at survey responses from respondents that have multiple memberships.

          I suggest articulating the particular business problem you are trying to solve and posting it to the Tableau forum.

          Steve

  2. Steve,

    I was introduced to your blog today at TCC13 and it is a great resource.  My organization has been visualizing survey data in Tableau for 4 years now and your suggestions will definitely help us improve our survey reporting.  

    As you can imagine, since we have been collecting survey data for several years users want to see trends from survey to survey.  Do you have any suggestions/best practices for incorporating trend data into the visualizations that you have showed us?

    Cheers,
    Jonathon
    Aspire Public Schools

    • Jonathon,
      Good question as none my examples address longitudinal data. Let me give some thought as to how to address this on the blog as I have done a fair amount of work here. I really need to put together some data sets as everything I have is client-specific.

      The good news is there’s great stuff you can do and it’s not very difficult.

      Steve

      • As most surveys have a respondent timestamp or some sort of collection ID that could be tied to a date, I imagine you could take questions like % saying yes, or the average Likert Score and plot that on a time series…

  3. Steve, a little late to the party here but very nice post. For the multiple tables approach, it looks like you joined two reshaped tables. I am curious how you did the reshaping on separate tables…did you reshape each one individually in Tableau and then copy back into Excel and re-connect? Or did you use some other tool (Alteryx)? To my knowledge there is no way to separately reshape data within a join (but please correct me if I’m mistaken!).

    Thanks,
    Alex

    • Alex, this is an old post. Two separate Excel files reshaped / pivoted using the Tableau Excel add-in.

      If I were doing it now I would use Alteryx.

      As for placing the resp ID on the level of detail and people possibly-selecting the bar, a trick I learned from Kelly is to place an invisible text box over the bar / bars. This was somebody can only select the table.

      I revisited this recently seeing if I could get what I wanted with a blend. I was not happy with the results.

      Steve

  4. Hi Steve,
    One other comment after having tested this approach on my data. If there are any nulls in your data I believe this method will encounter some difficulty since applying the Value filter to the entire datasource (and it doesn’t appear to work if it’s not applied to the entire datasource) will filter out those nulls and will therefore return incorrect percentages (in your Vote tab, for example).
    Alex

    • Steve,

      OK, last comment, I promise :)

      I was finally able to get this to work using the following method. This is a slight modification of the method Jonathan used in his famous post here: http://drawingwithnumbers.artisart.org/creating-a-dynamic-parameter-with-a-tableau-data-blend/

      First, I reshaped everything except the fields that I want to filter on (in this example, Country) and created that as my primary data source. I also reshaped the Country data, except then I filtered it (in Excel) to remove anything where value=0. I then removed the Value column, so all I have now are 2 columns: ID and Country, where a record in this data indicates a positive response for that country for a given user. My data now looks like this (again using your example):

      4 Mexico
      4 France
      4 Canada
      4 Germany
      4 UK
      4 USA
      5 Canada

      (note that rows where Value=0 before have been removed).

      Then I simply blended on the ID field (instead of using a “1” as Jonathan’s post suggests) and voila! Now you can filter on your Country field, and treat it like any other Quick Filter (multiple selecting, etc.). The filter limits the IDs that are available in the primary to those that satisfy the secondary’s filter.

      Hopefully this is helpful for other folks.

      Thanks Steve for your awesome posts – keep ’em coming!

      Alex

  5. Alex,

    Hey, keep the comments and ideas coming!

    I’ve been traveling and am just coming up for air. This looks like a very promising approach — would you mind e-mailing me a workbook and your modified source data?

    Steve

  6. Hi,

    I’ve followed your earlier posts and they’ve been really helpful in structuring my survey data. However, I haven’t been able to find a away to analyse/visualise multi-response questions in groups? Within my survey dataset I have about 8 “select all that apply” questions and 6 “single response” questions which have been organised in the structure you describe in part 1&2.

    I’m quite new to alteryx and tableau so apologies if this is stupidly simple.

    e.g. Q1. What is your favourite fruit? (select all that apply)

    I have the data structured so I can see the numbers of people who selected:
    A. Apple
    B. Pear,
    C. Lemon
    D. Lime
    E. Kiwi
    F. Banana
    G.Mango

    Say I also want to look at the number of people who have selected:
    H. Apple and Banana only
    I. Apple, Pear and Lemon only
    J.Kiwi and Lime only

    How would I do this?

    • Amy,

      I just want to clarify that for example H it would be Apple and Banana only so you would not want to see folks that perhaps selected a third or fourth fruit, correct.

      No, this is not stupidly simple and I’ll confess that I’ve only built something like this once or twice before and it’s going to take me some time to dig it up (and probably realize there’s a better way to do it).

      While you wait for me to get my act together may I suggest you post the question on the Tableau forum? I suspect you’ll get a good answer faster than I can provide right now.

      And do keep me posted on what you find out (and keep after mee to blog about this).

      Steve

      • That’s great thanks Steve.

        Regarding example H that’s correct, i only want to know the people who selected those two fruits and nothing else.

        Thanks

  7. Hello,

    I tried to understand the following: Probably completely simple, but as I am new on this, could not find how to do:

    We use input as suggested – demographics table – question / answer table.
    Question/answer table contains 3 questions where we calculate an satisfaction index on.
    Q1 *10%, Q2* 70% Q5 * 20%. Each question can have a value from 0 – 5.
    Q3 and Q4 do not count for our index.

    I used a calculation: IIF ([questionid]= ‘Q1’) THEN ([Answer] * 0.1)
    ELSEIF ([questionid]= ‘Q1’) THEN ([Answer] * 0.9)….END.
    This works fine to get overall satisfaction index.

    But I would like to build a view to answer “How many respondents (questionaires) have a satisfaction index 0-2, how many 3-4 and how many 5” and show this by my demographic dimensions. For this I would need the satisfaction index on level of questionaire. Is there a way to get this done without calculation outside tableau and using a separate consolidated data source?

    Thanks!

    • Berry,

      I think you can get what you want with a LoD calc where you include or fix at the respondent ID level. Can’t look at this right now (middle of some demanding projects) but it strikes me as a good topic for a blog post. In the meantime have you posted this to the Survey Group on the Tableau User Forum?

      If you don’t get an answer quickly send me an e-mail and I’ll try to have a look.

      Steve Wexler
      swexler@datarevelations.com

      • I will try – and I am looking forward for a new blog post, thanks for your input!

        • Berry,

          I believe I have it working — it was harder that I thought it would be, but LoD calcs take me longer than that should.

          I want to have some people vet the approach first, but will try to have something to you soon.

          Steve

    • Berry, I have a solution that works well. I will blog about it but am happy to set up a quick screen share to show you how it works.

      Steve

Leave a Reply to Nanditha Cancel reply

(required)

(required)