1 Reply Latest reply on Sep 27, 2016 4:34 PM by Santiago Sanchez

    multiple response categorical (or string) data

    Mary Jackson

      Hi,

       

      I'm new to Tableau and am trying to figure this out.  I'm used to running my data through SPSS that easily handles quantifying categorical data.  I've got data for a project where we're trying to assess the outreach programs my institution currently offers, including where they occur, the type of program that's involved and the strength of the relationship the department has with the outreach target group.  My dilemma is this, most of my data is categorical, but is translated from excel as string data, and the location data is multiple response categorical.  So for example, for each entry about an individual program, when asked to indicate which Washington counties the program occurs in, the respondent could select multiple counties for one individual program.  As a result there are 7 "County" data columns, meaning that at least one program entry occurs in seven different counties in WA state. 

       

      I'm having a couple problems.  I've seen how people use the pivot feature to handle multiple response data, but most of the tutorials seem to use numeric fields to run counts and sums rather than string.  If I wanted to be able to assess what % of the respondents (identified with individual ID #) had a program in a specific county how would I convert this data to be able to visualize the answer to this question?  I'm also interested in mapping the county data and use the "strength of partner" dimension as an extra filter.  I can figure out how to do this for "County_1", but when I tried some other data manipulations I found that the "strength of partnership" started to get counted as an entry for each individual county so it skewed the results for this dimension. 

       

      I've attached a small portion of my workbook data to demonstrate the data I'm working with.  Thanks for any help for a newbie.

       

      Thanks,

       

      Mary

        • 1. Re: multiple response categorical (or string) data
          Santiago Sanchez

          Hi Mary, welcome to Tableau and the community! Is the attached workbook something like what you're looking for? If so, here's what's done:

           

          1. Pivot all columns that may hold any responses. In other words, any 'county' columns:

          pivot.png

          2. Identify them as 'Question' and 'Answer'

          Q&A.png

          3. Since we've pivoted data, there are now multiple rows per ID. To avoid double counting these, we can create a calculated field.

           

               [Number of Respondents] = COUNTD([ID number])

           

          4. Finally, not all rows will hold a valid response, as some may have not been provided by the respondent. To exclude the empty rows, we can add a filter for 'Answer' and exclude nulls:

           

          filter.png

          1 of 1 people found this helpful