4 Replies Latest reply on Mar 14, 2016 10:24 AM by Roger Hau

    Help... need to create a view, of "multiple choice" survey data.

    Ariel Visor User Test

      Hello, I have to create charts for survey data. Normal charts are okey, no problem with them.
      To the point...
      The survey, have an answer like : 


        "What do you read newspaper for?"   (select all that apply)
          - Socials
          - Sports
          - News
          - Politics


      This should work withe the ONLINE, using a Live MySQL connection.  (the idea, is to not use complex mysql queries to obtain the results, as I would like the client itself be able to make the charts)

      See... for this question, the data table from the MySQL looks like this :

       

      IDOther QuestionOther QuestionWhat do you read newspaper for? (socials)What do you read newspaper for? (sports)What do you read newspaper for? (news)What do you read newspaper for? (politics)Other QuestionOther Question
      1Other OtherYesNoYesNoOtherOther
      2OtherOtherNoYesNoNoOtherOther
      3OtherOtherYesYesYesYesOtherOther
      4OtherOtherNoYesNoYesOtherOther

       

      So, each "ID" represents a single person who filled the survey.  There are other questions, and this multi choice questions, are represented each one in one column, with a Yes/No.
      I need to draw, in %, how much % of them use it for news, how much for sports, or how much for news. Like :

       

      Sports     [===========] 40%

      Social     [==============] 50%

      News      [======] 20%

      Politics   [=========] 30%

      None      [====] 15%

       

      The problem, is that, when I try to make the chart, I get like multiple charts... not a single One.
      I have read there is a pivot function to like merge the table in some way easy, but it does not work with MySQL... dunno why, when at the end, its a table anyway.
      Problem is that, for each ID, I could have multiple selections, so I could not make just a "What do you read newspaper for" column, with a "Sports, Social, News, Politics" options (this would be very easy to chart).

       

      Can anyone guide on how can this be done ?

       

       

      OR... ANOTHER OPTION....

      would it be possible, for the TABLEAU ONLINE, to have a "live" XLS connection to a file in a URL ?   (I mean, to use whatever function is available for xls, that is not for mysql, like the pivot?) But I have not seen the option to link to a URL XLS, only to a local file, that is later "extracted" when you publish to the ONLINE.

       

      BTW...
      I cannot change the way the MySQL table is shown, as this would be dinamic, depending on the questions asked... so I cant "pre-process" the MySQL out of tableau, or I cant "pre-process" any XLS in any way, as this would be needed to be REAL TIME ... (chart is being done, while survey is being run, automatically.)

       

      Thanks in advance.

        • 1. Re: Help... need to create a view, of "multiple choice" survey data.
          Shinichiro Murakami

          Not quite sure I understand you request correctly, but here is my approach.

           

          To calculate % of each category,

          you can use below formula with LOD   Overview: Level of Detail Expressions

           

          [News %]

          count(

          if [What do you read newspaper for? (news)]="Yes"

          then [ID] end

          )

          /

          attr({fixed:countd([ID])})

           

          [politics%]

          count(

          if [What do you read newspaper for? (politics)]="Yes"

          then [ID] end

          )

          /

          attr({fixed:countd([ID])})

           

          [Socials%]

          count(

          if [What do you read newspaper for? (socials)]="Yes"

          then [ID] end

          )

          /

          attr({fixed:countd([ID])})

           

           

          [Sport%]

          count(

          if [What do you read newspaper for? (sports)]="Yes"

          then [ID] end

          )

          /

          attr({fixed:countd([ID])})

           

           

          Now, "None" is a bit tricky.

          [None]

          if {fixed[ID]:(

          countd(

          if [What do you read newspaper for? (news)]<>"Yes"

          then [ID]  END )

          *

          countd(

          if [What do you read newspaper for? (politics)]<>"Yes"

          then [ID]  END )

          *

          countd(

          if [What do you read newspaper for? (socials)]<>"Yes"

          then [ID]  END )

          *

          countd(

          if [What do you read newspaper for? (sports)]<>"Yes"

          then [ID]  END )

          )}

          =1

          then "Yes" end

           

           

          [None %]

          count(

          if [None]="Yes"

          then [ID] end

          /

          attr({fixed:countd([ID])})

           

          I tested None % calculation is correct through dummy column of

          [No News,Politics]  and [No News,Politics %]

           

          Thanks,

          Shin

           

          9.0 attached.

          • 2. Re: Help... need to create a view, of "multiple choice" survey data.
            Ariel Visor User Test

            Anyone ? ...

             

            I have sucesfull do this with a XLS file, but the problem is that I need to make this "live" on the ONLINE...  (either from a XLS file from a URL, or directly from a MySQL DB).


            This how it looks from a XLS, using the pivot function, and just adding a measure "checkall" to count the "yes" instances.

             

            I think the MAIN PROBLEM, is that PIVOT FUNCTION is NOT SHOWN when the data source is the MySQL table.  (even, when the table is exactly the same as the XLS file)

             

             

            ariel.sepulveda.0 escribió:

             

            Hello, I have to create charts for survey data. Normal charts are okey, no problem with them.
            To the point...
            The survey, have an answer like : 


              "What do you read newspaper for?"   (select all that apply)
                - Socials
                - Sports
                - News
                - Politics


            This should work withe the ONLINE, using a Live MySQL connection.  (the idea, is to not use complex mysql queries to obtain the results, as I would like the client itself be able to make the charts)

            See... for this question, the data table from the MySQL looks like this :

             

            ID
            Other Question
            Other Question
            What do you read newspaper for? (socials)
            What do you read newspaper for? (sports)
            What do you read newspaper for? (news)
            What do you read newspaper for? (politics)
            Other Question
            Other Question
            1Other OtherYesNoYesNoOtherOther
            2OtherOtherNoYesNoNoOtherOther
            3OtherOtherYesYesYesYesOtherOther
            4OtherOtherNoYesNoYesOtherOther

             

            So, each "ID" represents a single person who filled the survey.  There are other questions, and this multi choice questions, are represented each one in one column, with a Yes/No.
            I need to draw, in %, how much % of them use it for news, how much for sports, or how much for news. Like :

             

            Sports     [===========] 40%

            Social     [==============] 50%

            News      [======] 20%

            Politics   [=========] 30%

            None      [====] 15%

             

            The problem, is that, when I try to make the chart, I get like multiple charts... not a single One.
            I have read there is a pivot function to like merge the table in some way easy, but it does not work with MySQL... dunno why, when at the end, its a table anyway.
            Problem is that, for each ID, I could have multiple selections, so I could not make just a "What do you read newspaper for" column, with a "Sports, Social, News, Politics" options (this would be very easy to chart).

             

            Can anyone guide on how can this be done ?

             

             

            OR... ANOTHER OPTION....

            would it be possible, for the TABLEAU ONLINE, to have a "live" XLS connection to a file in a URL ?   (I mean, to use whatever function is available for xls, that is not for mysql, like the pivot?) But I have not seen the option to link to a URL XLS, only to a local file, that is later "extracted" when you publish to the ONLINE.

             

            BTW...
            I cannot change the way the MySQL table is shown, as this would be dinamic, depending on the questions asked... so I cant "pre-process" the MySQL out of tableau, or I cant "pre-process" any XLS in any way, as this would be needed to be REAL TIME ... (chart is being done, while survey is being run, automatically.)

             

            Thanks in advance.

            Demo Data loaded in a XLS  (before pivot) :

             

            Pivot done to selected fields

             

            CheckAll content  (this basically, counts "yes" of each option, divided by the total count of ID, to get a real % based on total number of IDs as on the original survey data.)

            SUM(

            IIF (Left([Response],1)="S" ,1,0)

            )

            /COUNTD([ID])

             

             

            NOW AGAIN.... problem is HOW TO DO THIS EXACT THING, using MySQL ??

            is there a way to enable the pivot option??

            • 3. Re: Help... need to create a view, of "multiple choice" survey data.
              Ariel Visor User Test

              Shinichiro Murakami :

              I will check with the live MySQL, I think it should work.... but It would get very complex for real world survey (when, for example, I have around 150 of this type of columns, for different charts, on the same report.)

               

              • 4. Re: Help... need to create a view, of "multiple choice" survey data.
                Roger Hau

                Hi Ariel,

                 

                MySQL does not natively support the Pivot operator (or unpivot query): http://stackoverflow.com/questions/15184381/mysql-turn-table-into-different-table 

                 

                That same StackOverflow post shows a way that you can use Custom SQL and the UNION ALL query to achieve what you're looking for. If you need some help, drag your table into the join area, then convert to Custom SQL:

                 

                You'll see a dialog come up like this:

                3-14-2016 10-20-15 AM.png

                 

                You can then write the SQL to achieve your pivot. If the SQL is sensitive, please reach out to me at rhau@tableau.com and I can assist you privately.

                 

                At TC15, we demoed union for text/Excel, which is now in the 9.3 Beta. We're still working on database union, and that'll make this flow much smoother in the future. Further out, we'll think of a way to make the MySQL pivot scenarios better!

                 

                Thanks,

                -Roger