1 Reply Latest reply on Sep 12, 2018 5:11 PM by Esther Aller

    How to cross tabulate 2 multiple response variables in a pivoted dat set

    Chris Storey


      I am new (again) to Tableau.  I have looked in the past and had difficulties with multiple response survey data and always left unable to get anything done.  I am back after seeing data prep and I feel like tableau can do what I need, I just don't know how.


      I am not sure if my query is about Prep or Desktop.  I have used both, I'm not getting what I need but I don't know if the answer is in how to Prep or how to build the Dashboard in Desktop.


      I started with a 'short-wide' file of survey test data in xls.  My data is 1 row per record and contains some multiple response data which is delimited.


      I took that data into Prep and split the multiple response variables by the delimiter.  So for my healthcare data e.g. the multiple current drugs a patient is on I now have multiple single response variables.

      I then Pivoted the data to make it 'long-skinny'.  I pivoted all variables except for ID's.   I do not understand whether everything except ID should be pivoted or whether it is advisable for some other variables to remain outside of the pivot


      After the pivot I Grouped the split out multi-response data and sent it to tableau desktop.


      In Desktop I can filter on the Grouped drugs from the pivot and see that as a multi.

      But I cant work out how to cross tab the multi response drugs data by other variables inside the pivoted data.  I would like to be able to cross - tab it by some single response and also some multiple response data.

      Another of my multiple response fields is an overlapping time period variable for moving quarters.  I know there are ways to make such variables from a single date field in Tableau but I would rather use what I have built already (and this is just an example of many multiple response variables I might need to use).



      In Desktop I have

           Rows     Pivot Values

           Columns     AGG (Total patients)        Calculated field of Countd(ID)


                     Grouping      drug variable      (the multiple response)

                     Pivot1 values (excluding blanks from my drug variable)



      Within the Pivot1 I also have other variables that I want to cross against these drugs - how do I do that ?



      Hope you can help.  Thanks