In the previous article, I shared with you how you can deal with multiple response survey questions in Tableau Desktop. In this article, I want to show you how you could have prepared the same data in Tableau Prep – before loading it to Tableau Desktop for analysis.
Just like in the previous article, I’ll be using the same question on social media channels - which is a multiple response question.
Quiz: Which of the following social media channels did you use in the last one month?
Responses - (Facebook, LinkedIn, YouTube, TikTok, WhatsApp, WeChat, Twitter, Reddit, Pinterest, SnapChat & Instagram)
Sample data – Table 1.0
(This data could be collected in the above format - whereby, under the various possible responses - you’ve values (in this case the value is 1) indicating that respondents agree to have used such social media channels over the last one month).
Sample data – Tableau 2.0
(Alternatively, this data could be collected in the above format – whereby the multiple responses are separated by a semi-colon).
So, how do you prepare these sample data sets in Tableau Prep?
Solution one
Assuming the data you’re working with is collected in the format of the first table. You can prepare it as follows.
Connect the data to Tableau Prep.
Add a Pivot Step, as shown below.
Drag the social media channels from the Fields area to the Pivoted Fields area.
Add an output step to export your data for visualizing in Tableau Desktop.
You can export the data as a CSV file, Microsoft Excel (.xlsx) or a Tableau Data Extract (.hyper) for use in Tableau Desktop.
Note that the exported data resembles the previous sample data prepared in Tableau Desktop and therefore you can visualize it the same way by (refer to the previous article).
Drag ‘Pivot1 Names’ to the rows.
Drag ‘Pivot1 Values’ to the columns.
Divide SUM(Pivot1 Values)/COUNTD(Unique_ID) – simply to get the proportion of total.
Format the labels into ‘Percentage’
Solution two
Assuming the data you’re working with is collected in the format of the second table. You can prepare it as follows.
Connect the sample data and add a Clean Step.
On the field containing the multiple response answers – chose custom split.
On the pop-up menu, use semicolon as the separator (;) and split-off all the values.
Next add a pivot step. And drag the fields we’ve split above from the Fields area to the Pivoted Fields area.
Add an output step and export your data for visualizing in Tableau Desktop.
On the Tableau Desktop you can analyze the data by.
Drag the field ‘Which social media channel did you use in the last month? Split’ to the rows.
Drag COUNTD(Unique_ID) to the columns (to show the unique respondents who mentioned the various social media channels).
Add table calculation ‘Percent of total’
Exclude the NULLs from the view.
I hope this article was helpful to you. To receive more of the Tableau tips, kindly join our mailing list by subscribing below.
If you like the work we do and would like to work with us, drop us an email on our Contacts page and we’ll reach out!
Thank you for reading!
