1 2 Previous Next 25 Replies Latest reply on Feb 23, 2017 1:16 PM by Jim Dehner

    Multiple Response Calculation

    Kevin Jessop

      Hi there,

       

      I'm sure this is an easy one, but I can't quite get there.  Tableau, v9.2, survey data has been pivoted.

       

      Essentially, I'm trying to get Tableau to treat a certain question as a multiple response question.  However, it's treating it as a single response, so my actual counts are correct but the percentage is wrong since Tableau isn't calculating based on the number of people who answered the question. 

       

      A: 200

      B: 150

      C: 100

       

      Above is an example - those are the number of people answering each answer option.  I'd like Tableau to calculate % based upon the number of people answering that particular question, not the total number of people answering (e.g. Tableau adds up all responses and uses that as the base size (450 in this example).

       

      The calculation being used (the incorrect one), is:

       

      SUM([Weight2]) / TOTAL(SUM([Weight2]))

       

      Weight2 is a weighted field.

       

      I do have a unique ID field, I suspect I need to integrate that is some way?

       

      The multiple response question is not set up as a Yes/No type of question.  Each response has 8 choices.  I wonder if that's the problem?

       

      Oh, I have added the multiple response question by going into my pivot field dimension, and simply selecting my 10 "questions" - each question having the 8 response mentioned above.

       

      Any ideas?

        • 1. Re: Multiple Response Calculation
          Kevin Jessop

          Edit: It definitely has something to do with the % being calculated as a Table Down calc.

          • 2. Re: Multiple Response Calculation
            Jim Dehner

            Hi Kevin

             

            Do you have a workbook ****.twbx that you could post -

            I think you will need an LOD expression that calculates your percentage over each question - not clear what your Dimensions and Measures are from the above -

             

            but it could take the form of Fixed  {Question] : SUM([Weight2]) / TOTAL(SUM([Weight2]))}

            but that is only a guess - not sure what weight 2 is

             

            Jim

            • 3. Re: Multiple Response Calculation
              Kevin Jessop

              Hey Jim!

               

              Thanks so much for the response.  Your formula looks like the kind of thing I'm after, sir.  I'll try that.

               

              Posting this one as a .twbx might be a bit of a pain - super confidential survey data for a big client.  I'll see if there is a way I can anonymize it.

               

              Thanks again for your suggestion.  I'll post the result!

              • 4. Re: Multiple Response Calculation
                Jim Dehner

                Ok give it a try - I do have a question about your data though -

                I have done work on survey data and very often the responses to a given question are in columns - that is 8 possible responses to a questions are in 8 different columns.  If your data is in that format you will probably want to pivot the data for each question so that you end up with data in rows - that way the question will show up as a Dimension and the responses will be a Measure

                 

                Let me know

                 

                Jim

                • 5. Re: Multiple Response Calculation
                  Kevin Jessop

                  Hey Jim,

                   

                  Great question/comment. Yessir, the data is pivoted successfully and integrated into the dataset. 

                   

                  I've tested this 2 ways now and keep getting the same result.  2 versions of the same multiple response question.  I've tested using the following formats:  Example (fake) question - what's your fave color:

                   

                  Version A:

                  Response 1: Red (1) White (2) Green (3) Black (4)

                  Response 2: Red (1) White (2) Green (3) Black (4)

                  Response 3: Red (1) White (2) Green (3) Black (4)

                  Response 4: Red (1) White (2) Green (3) Black (4)

                   

                  Version B:

                  Red (1)

                  White (2)

                  Green (3)

                  Black (4)

                   

                  Both formats yield the same response, i.e. the counts (frequency) is correct, but the percentages are whacky.  I think the problem is that this multiple response question was only asked for people who answered another question a certain way.  I'd like my percentages to be calculated based upon the total number of people answering that question.  I can do this easily in SPSS - but this is killing me!

                  • 6. Re: Multiple Response Calculation
                    Jim Dehner

                    OK I have a solution for you and will walk you through it - I am on t10.1 see the attached excel sheet that I used as input

                    several points:

                    1. First you want you data file to be a single record for each respondent (disagregated
                    2. Yes the fact that you have dependency between questions complicates the process
                    3. you will need to calculate the number of responses that you want to use as the base for each of the dependent questions using an LOD expression
                    4. I had 3 questions Age, Color, Fruit - Only respondents age 30 could respond to the color questions I further counted ages 30-40 as the basis for % color calculation - all could respond to Fruit including a 50 year old
                    5. This is used as the basis for the color % >> { exclude [Q1 Color]:     sum( (IF [Age]>=30 and [Age]<=40 then   ([Number of Records]) End))}

                      1. The % calculation is   >> count([Q1 Color])/sum([Count of age 30-40])
                    6. Likewise the calculation for the basis for % of Fruit >>   { exclude [Q1 Color], [Q2 Fruit]:    sum([Number of Records])}

                      1. The % calc is >> count([Q2 Fruit])/sum([count of all respondents])
                    7. When you do that you should end up with something that looks like this
                    8. You count the color or fruit responses with a zn formula  like this for color    >>zn(count([Q1 Color]))

                     

                    let me know if this helps

                     

                    Jim

                    • 7. Re: Multiple Response Calculation
                      Kevin Jessop

                      Hey Jim - this is great.  I'm looking into this - I'll update the thread with my progress, sir! 

                       

                      Thank you again.

                      • 8. Re: Multiple Response Calculation
                        Jim Dehner

                        Thanks

                        I am going to try to convert the T10.1 file I have to a TWB 9.2 file that you can see - I am not sure about the conversion tool but I will give it a try

                        Jim

                        • 9. Re: Multiple Response Calculation
                          Jim Dehner

                          See if you can open this - at least you can see what I did

                           

                          Jim

                          • 10. Re: Multiple Response Calculation
                            Kevin Jessop

                            Jim - most excellent.  The file works perfectly and is extremely helpful. 

                             

                            You're totally right - I need a calculation which calculates my base size for that particular question, and then incorporate that base size to correctly calculate the percentages.

                             

                            This is so close! I think the trick is, all my data has been pivoted.  Tell you what, I'm going to see if I can re-create something similar to my actual file, and then upload.

                             

                            Jim, you've been really gracious with your time, sir.  

                            • 11. Re: Multiple Response Calculation
                              Jim Dehner

                              Thanks - glad to help you out

                              If you have any more questions just let me know - my profile is posted

                               

                              BTW - if you get a chance please mark the original response as correct response

                               

                              Thanks

                              Jim

                              • 12. Re: Multiple Response Calculation
                                Kevin Jessop

                                Jim,

                                 

                                Thanks again, this totally helped.  I have a part 2...I'll post it here but maybe I'll move it since it might be a different thing.

                                 

                                The weighted data seems to be problematic on one type of question.  I have a Measure, WeightedData2.  I am using this for all my calculations and it's working out great.

                                 

                                The only one it's not working out with is this particular question you've been helping me on.  The problem is this: Tableau is using my unweighted data for the base size instead of the weighted data.

                                 

                                So, while the Number of Records measure successfully shows the total number of people answering the question, it's not the weighted number of records.

                                 

                                For the life of me, I can't figure out how to incorporate the Number of Records and Weight measures to work together.  Any ideas?

                                • 13. Re: Multiple Response Calculation
                                  Jim Dehner

                                  Thanks

                                   

                                  I think the weighting issue comes from the formulas I sent you they are based on record count

                                   

                                  Count of age 30 -40 >>{ exclude [Q1 Color]:     sum( (IF[Age]>=30 and [Age]<=40

                                  then   ([Number of Records])

                                  End))}

                                   

                                   

                                  % Color >> count([Q1 Color])/sum([Count of age 30-40])

                                   

                                   

                                  Which in effect does not weight the response - If you change that number of records to some weighted measure it should fix the issue

                                   

                                  Let me know

                                   

                                  Jim

                                  • 14. Re: Multiple Response Calculation
                                    Kevin Jessop

                                    EDIT: Number of records perhaps isn't accurate.  Here's how I have things set up:

                                     

                                    2 calculated fields:

                                     

                                    Response:

                                    sum([Weight2])/([Totaler])

                                     

                                    Totaler

                                    TOTAL(COUNTD([ID])) <--this is what I'm using to calculate the overall number of people answering the particular question (i.e. the questions base size)

                                     

                                    The problem is my Totaler field is unweighted - that's what I need to be weighted!  For the life of me I can't figure it out.

                                    1 2 Previous Next