6 Replies Latest reply on Aug 16, 2012 9:39 AM by sophiegaudet

    Table calculation of summed counts

      I would need to create a calculated field that represents 5 "columns" or variables of dichotomous data. E.g. data is represented like so:


      I need a calculated field that tells me how many people (in this case each row represents a respondent) indicated "4", how many said "3", etc., not in each column but in total (across all columns). In the end I want a representation of how many people said "1", "2", etc as a bar graph.



        • 1. Re: Table calculation of summed counts
          Mark Holtz

          Are you able to change the structure of your data?

          I think it would be easier to accomplish this if you had un-pivoted data...


          I'm thinking:

          RespondentID     QuestionNum     Response

          1                         1                         NULL

          1                         2                         2

          1                         3                         1              

          2                         1                         4

          2                         2                         4

          2                         3                         3

          ...and so on


          That way, you can use the response as a dimension and group by it, providing the count of records that each has.

          You can also arrange a cross tab to show the data as you have it above by placing RespondentID in the Rows Shelf, the QuestionNum in the Columns Shelf, and the Response in the Mark Shelf as text.

          • 2. Re: Table calculation of summed counts

            Was affraid you'd suggest that -  the data set contains nearly 300 seperate "QuestionNum" for 500 respondents/cases. I am assuming that that will fit in the spredsheets?


            Not entirely clear, however, how doing this will sum up the counts from 5 different "QuestionNum" for each "RespondentID"? By putting the Response on the "Mark shelf" it will automatically sum what I filter in there?



            • 3. Re: Table calculation of summed counts
              Mark Holtz

              If you have Excel 2007 or later, you should be able to explode to 150,000 (300x500) records.


              Tableau will give you its default "Number of Records" measure, which you would be able to use to tally counts of responses.


              You can then create additional measures in Tableau to tally each response. (assuming always 1-5).



              IF Response=1 THEN 1 ELSE 0 END



              IF Response=2 THEN 1 ELSE 0 END


              and so on...

              • 4. Re: Table calculation of summed counts

                This would probably work... except I can't get Tableau to communicate with my un-pivoted exel file. I have errors that occur. At this point, I don't have the time to explore the errors or do anything about them, especially when Tableau will communicate with the pivoted exel file. My deadlines are far too tight.


                While I am aware that it may be easier the way you suggest, is there a calculation that would allow me to do the originally presented question? E.g., Sum of counts from Q1, Q2 and Q3 to get to X # of people said "1", X being the sum from 3 different questions.


                If there isn't one, please let me know and I will do the calculations in SPSS or exel and move out of Tableau.



                • 5. Re: Table calculation of summed counts
                  Mark Holtz

                  You can always create calculated fields that give you conditional counts.

                  Supposing that your current measures are Q1, Q2 and Q3, and that you want to have counts of the number of records where there is at least one "1", at least one "2", at least one "3" then you can create 5 calculated fields:


                  "Respondents who gave a 1"

                  IF [Q1]=1 OR [Q2]=1 OR [Q3]=1

                  THEN 1

                  ELSE 0



                  "Respondents who gave a 5"

                  IF [Q1]=5 OR [Q2]=5 OR [Q3]=5

                  THEN 1

                  ELSE 0



                  Of course, the more difficult thing is to answer the question "how many 1's were used as responses overall."

                  To do that, you'd have to account for all permutations per row... If you only have 3 questions, it's not so bad.

                  "Number of 1's":

                  IF ([Q1]=1 AND [Q2]<>1 AND [Q2]<>1)

                  OR ([Q1]<>1 AND [Q2]=1 AND [Q2]<>1)

                  OR ([Q1]<>1 AND [Q2]<>1 AND [Q2]=1)

                  THEN 1

                  ELSEIF ([Q1]=1 AND [Q2]=1 AND [Q2]<>1)

                  OR ([Q1]=1 AND [Q2]<>1 AND [Q2]=1)

                  OR ([Q1]<>1 AND [Q2]=1 AND [Q2]=1)

                  THEN 2

                  ELSEIF ([Q1]=1 AND [Q2]=1 AND [Q2]=1)

                  THEN 3

                  ELSE 0



                  Hope this helps.

                  • 6. Re: Table calculation of summed counts

                    That was perfect - the former calculation worked great. Started with that since in actuality, I have up to 10 options in 5 different questions... 


                    Thank you very much!