1 Reply Latest reply on Sep 25, 2018 8:14 PM by Jonathan Brough

    Shaping A Checkbox Survey Question in Tableau

    Jonathan Bechtel

      Hello everyone,

       

      I'm trying to shape survey data inside Tableau and ran into a problem with a checkbox question.

       

      The survey I'm referring to in this post can be seen here:  Sample Tableau Form

       

      I'm having problems with the 5th question (Which of Our Products Do You Use?  Check All That Apply). 

       

      It gets stored in the google spreadsheet like this:

      google_survey_sheet.PNG

      This is problematic because I'd prefer to have each answer itemized and counted separately as its own question.  Ie, a "Yes/No" or "0, 1" answer to whether or not someone has each of the individual products based on their answers.   Or something that would be functionally equivalent to this.

       

      I thought doing a Split + Pivot would do the trick, but this turns out not to work because you can't pivot columns that are dimensions.

       

      Ie, if I do a custom split on the "," separator for all columns I'm left with this: 

       

      google_survey_sheet_2.PNG

      And I can't pivot that into a single column, which would solve a lot of my issues. 

       

      Does anyone know how to go about doing this using just Tableau and not Tableau Prep?

       

      I prefer not to use Excel or 3rd party tools. 

       

      Is this the sort of task that's accomplished much more easily via Prep than Desktop?

       

      Thank you.

       

      Attached is the survey data in spreadsheet format.

        • 1. Re: Shaping A Checkbox Survey Question in Tableau
          Jonathan Brough

          Hi Jonathan,

          You essentially want to do a Split, to get it into separate columns, and then a Pivot, to get those entries into separate rows. However, Tableau doesn't appear to allow the pivoting of Split fields.

          You could try to either:

          1. set up a finite list of answers and then do a join to that list (see this post - https://community.tableau.com/message/675882#675882). Not sure if this will work with Excel though.

          2. do the split in the Excel file. using these three steps:

          a. edit Excel to:

          - use Find command in Excel (see attached, columns M onwards)

          - include a new RespID column as an index of the responses (to use in Tableau calculations)

          b. pivot answer columns in Tableau, within Data Source preview area

          c. Filter out false answers (see attached Word document for screenshots)

          d. Set up new calculations that use RespID

          - count distinct of RespID for 'Count of Responses'

          - Level of Detail calculations for the 4 quantitative responses (Comfortable recommending, Remain customer etc.).

             These act as "For each response, take the lowest response", because there can now be multiple rows for each respondent.

             Make sure you do a similar LOD calculation for Age if you plan to average it.

          Please mark answered if this works for you.

          Thanks,

          Jonathan