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 of 2 people found this helpful
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.
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!
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