Hi Jessica - It seems to me that what might work is replacing the N/A options with null values (not zeros)? Probably easier to do this outside of Tableau, but you could do it in a calculate field. Then you'd have more control over what values should be null, based on the question. I do this in SAS, for instance, with arrays, to convert all N/A responses to null, depending on the question (because, like you, sometimes it's the fifth option and sometimes it's the eighth).
Hope that helps!
Thank you for the suggestion! The only problem is that then, we are unable to determine which respondents actually skipped the item vs. responded with "N/A." (It might seem silly, but it matters to the researchers in this instance...)
Hmm... Can you create a new variable, and use one to visualize the stacked bar, and the other to calculate the means? It might be a pain if it's already in Tableau, but if you could do it outside and update your extract to just include the new column that could work, I think?
Hmm... I think that might work! Let me go putz around and see what happens. Thank you for the idea!
Steve Wexler's post that you linked to didn't have any means or SD, did you mean to link to a different post?
This can all be done in Tableau, you can create multiple measures in Tableau and use them in different places in a view. Let's assume:
RespondentID - uniquely identifies a respondent
ResponseID - uniquely identifies a response
QuestionID - unique question identifier
AnswerValue - is the coded number answer value
So COUNT(ResponseID) is the total number of responses, COUNTD(RespondentID) is the total number of respondents.
We need to create a calculation to separate out the NAs, a measure for "Usable Responses" could be created. For the purposes of this example there are three QuestionIDs where for QuestionID 1 an Answer Value of 5 is NA, for QuestionID 2 an AnswerValue of 6 is NA, and so on.
WHEN 1 THEN IF [Answer Value] != 5 THEN [ResponseID] END
WHEN 2 THEN IF [Answer Value] != 6 THEN [ResponseID] END
WHEN 3 THEN IF [Answer Value] != 7 THEN [ResponseID] END
Then COUNT([UsableResponses]) only counts the non-NA responses and that measure can then be used to generate reference lines for means, SD, etc. while the original COUNT([ResponseID]) measure is used for the overall numbers.
What I typically do for survey data is create another table that has QuestionID and AnswerValue as the key with additional columns for additional attributes like the actual answer text that you seem to already have *and* an indicator that says whether the particular QuestionID/AnswerValue is usable or not. I also use this kind of setup for coding particular QuestionID/AnswerValue combos as to how they are included in top box/bottom box scores.
Thanks for your response! Somehow I did not get a notification when you posted this, so I'm just now seeing your reply.
I ended up doing something similar to part of your suggestion, but instead of using Answer Value, I used Answer Text, so that I could specify that "Not Applicable" itself be excluded from Likert value means and SDs (rather than relying on the arbitrary coding of the N/A answer options):
IF [Text] = "Not applicable" THEN NULL ELSE [Likert Values] END