How to visualize multiple answer questions in tableau

Question

I have a question, How do you handle and visualise multiple answer questions in tableau. If you have a dimension Could you please tell us where we need to improve? and the questions and the choices are

Explosives, Wireless

Vehicles

Cement, Vehicles.

I want to calculate the no of times vehicles is selected in the answer. How do I do that?

Alex's answer should work just to count vehicles. Is that all you want to do or do you want to count vehicles, count cement, etc? — Andy TAR Sols, Aug 01 '20 at 06:34
Yea I want to count vehicles, count cement etc, but even the count which I am doing I am getting a count of zero which is confusing, you can see in the pic how the different values are seen — sandeep pradhan, Aug 01 '20 at 13:30
What are the maximum number of separated values in the list? — Andy TAR Sols, Aug 01 '20 at 13:42

Alex Blakemore · Answer 1 · 2020-08-07T04:11:28.003

2

One way is to define a calculated field as below that has the value 1 for data records that contain the string "vehicles" in the field [My Field], regardless of which characters are upper or lower case. Let's Say you call this calculated field Has Vehicles

int(contains(lower([My Field]), “vehicles”))

Then if you drag the calculated field you just defined to a shelf as a measure, then you can count the number of records that contain that string, with the aggregation function, SUM - as in SUM([Has Vehicles])

You can use the field as dimension or filter instead to separate records that have vehicles from those that don't. Or use other aggregation functions to determine the percentage of records that have vehicles, using AVG() instead of SUM(), since the the field only has values 0 or 1. Or use MIN() or MAX() or STDEV() etc.

You can also use a parameter for your text string to allow the user to type or choose different strings, instead of hard coding it to the string "vehicles"

For more complex text analytics, consider using regular expression functions instead of contains, or doing some pre-processing with Tableau Prep, Python or other tools to clean and normalize the text data up front.

edited Aug 07 '20 at 04:11

answered Jul 30 '20 at 05:41

Alex Blakemore

11,301
2
26
49

2

Please don't post only code as answer, but also provide an explanation what your code does and how it solves the problem of the question. Answers with an explanation are usually more helpful and of better quality, and are more likely to attract upvotes. – Dima Kozhevin Jul 30 '20 at 06:42
I tried the above code but each time the sum shows 0 . what could be going wrong. I added a pic for your reference – sandeep pradhan Jul 31 '20 at 16:51
Can you post a link to your workbook and sample data, say at Tableau Public? – Alex Blakemore Aug 03 '20 at 17:30
P.S. the best approach in production environments with problems like this is to perform some data prep/reshaping upstream to make the analysis/visualization stage more accurate, reliable and efficient. You really seem to have two tables crammed into one, instead of the (more normalized) 2 tables with a one-to-many relationship between them. – Alex Blakemore Aug 03 '20 at 17:32
Alex I cant upload it in tabeau public but you can send me your email address and I can send it to you? – sandeep pradhan Aug 05 '20 at 03:39
I’m not sure of a way to get you my email address without posting it publicly. Why not just publish a simplified small example on Tableau public, using notional data? That way people could respond with a working example. – Alex Blakemore Aug 05 '20 at 18:49
I wanted to give you the workbook because it uses sharepoint lists as a data source and the tables populated there are different . I will give you my email address and you can respond there. it is sandeep12d3@gmail.com. Hope this works? – sandeep pradhan Aug 07 '20 at 04:52

score 0 · Answer 2 · answered Aug 02 '20 at 06:30

As there are up to 6 separated values, using this link, https://www.flerlagetwins.com/2020/05/split-and-pivot.html, you need to split and union the data.

Splitting the field will produce 6 new fields.

Union the table to itself 6 times, then write a new calculated field to bring back 1 of the "splits" per union. In the link it is something like:

CASE [Table Name]
WHEN "Events" THEN [Split 1]
WHEN "Events1" THEN [Split 2]
WHEN "Events2" THEN [Split 3]
...
WHEN "Events5" THEN [Split 6]
END

Looking at your data you will also have to tdy the values, removing "" and spaces. Look at the TRIM and REPLACE functions.

If you use this technique, be sure to filter out rows that have null values in that final combined field — I.e. the one defined by the case statement. — Alex Blakemore, Aug 03 '20 at 20:53

How to visualize multiple answer questions in tableau

2 Answers2