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...
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.
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?
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...
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.
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
"Respondents who gave a 5"
IF [Q1]=5 OR [Q2]=5 OR [Q3]=5
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)
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)
ELSEIF ([Q1]=1 AND [Q2]=1 AND [Q2]=1)
Hope this helps.
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!