6 Replies Latest reply on Feb 17, 2016 8:53 AM by Toby Erkson

    "Check all that apply" survey questions

    Ted Cuzzillo

      I have survey results with several questions of the "check all that apply" variety. (You know the type: "Which movies do you like? A. Fargo B. Godfather ... and so on.) How do you make the data show percent of total responses for each choice? If, for example, everyone likes Fargo and everyone likes Godfather, both would show up as 100%.

        • 1. Re: "Check all that apply" survey questions
          Shawn Wallwork

          Hi Ted it will depend a lot on how your data is laid out. Do you have a sample Excel file you could post?



          • 2. Re: "Check all that apply" survey questions
            Mark Andrews

            Here is an example were I have 2 questions.  Q2 and Q4 are "Check all that apply" type questions.

            • 3. Re: "Check all that apply" survey questions
              Susan Baier

              Ted and Mark, in order to make this work you're going to need to reshape your data so that your question titles are in one column, and your responses are in another. I've attached a workbook that showed what I did, but here's the rundown:


              1) Replace all "-" with blank cells (this makes calculations easier in my opinion)

              2) Since these are "select all that apply" responses, and show up in the Excel file as the item name whenever it's been selected, we need to replace responses for items we want reshaped with numbers -- this is necessary so you can group multiple questions together in a chart. I used a "1", but you can use any number as long as it's consistent for representing "selected this item"

              3) Move the items we want to reshape to the right side of the worksheet, and the ones we don't need to reshape (those that are unique and will be represented solely as answers to one particular question, like "City", or open-ended responses) to the left

              4) Once we've got the Tableau add-in in Excel, we put the cursor in cell E1 since we want to reshape everything in row E and to the right. Click "Reshape data" in the Tableau add-in.

              5) The new sheet of reshaped data is called "Sheet1 - Tableau". We rename column E "Question" and column F "Response". As you can see, there are now multiple rows per respondent.

              6) Open a new workbook in Tableau and connect to the data. Make sure you're connecting to the Sheet1-Tableau sheet.


              Reshaping the data gives us a Question dimension, and a Response dimension. I created a Question (group) dimension to make it easy to select either the region questions, or the testing ones. I then created both table and bar chart versions of the results.


              I've attached the workbook so you can see what I did, and hopefully figure it out by reverse-engineering. If you have any questions let me know.



              2 of 2 people found this helpful
              • 4. Re: "Check all that apply" survey questions
                Susan Baier

                Also Ted and Mark, join us in the Survey Data group if you have more questions about working with this kind of data in Tableau! 



                • 5. Re: "Check all that apply" survey questions
                  Theodor Klemming

                  Hi Ted


                  I see that your question is still marked as Unanswered. Maybe you are no longer monitoring the community.


                  In any case, let me offer an alternative solution, where I use a tool from Datawatch called Monarch Personal, which is free of charge:



                  Your use case is a classic case of WIDE data versus TALL data. Your data is in a wide format, or pivoted format, while you would need it in a tall format, or unpivoted format, for your analysis.


                  One easy way of solving that is to use Datawatch Monarch, which allows you to reshape and transform data with ease.

                  In this case, you would like to apply an Unpivot Transform, and include columns D to O in the transform. The result will be columns A, B, C as they are, plus a column called Attribute containing all of the questions originally used as column titles in column D to O, and a column called Value containing the response give by each respondent to each question. Many of the rows in the Value column will contain just a dash character; these are the rows resulting from answer options that the respondents DID NOT select. You should remove or filter out all rows that contain only a dash character.


                  When analyzing the data, you need to know the total number of respondents. The "percent of total responses" number that you are looking for is the count of each unique answer value, divided by the total number of respondents.


                  By all means, download Datawatch Monarch Personal and try it out. If you have any questions, you are welcome to join our user community on http://community.datawatch.com

                  Datawatch is an official partner of Tableau Software.


                  Best regards, Theo Klemming, product expert at Datawatch Corporation

                  • 6. Re: "Check all that apply" survey questions
                    Toby Erkson

                    Seeing that Ted's last login was BACK IN 2013

                    and not a single response to any other posts I would say it's a fair guess that he's not monitoring the forums