6 Replies Latest reply on Jun 16, 2016 12:36 PM by Jessica Schuett

    Exclude a value from a calculation, but not from a count.

    Jessica Schuett

      Hello!

       

      I've created a visualization for results of a survey with many Likert-type items using a dual axis combination of stacked bars (to show percent of respondents selecting each response) and circles (to show mean/SD for each item) a la Steve Wexler (scroll to about halfway down the page for an example). However, many of these survey items also have a "not applicable" response option. I want to exclude the N/A value from the mean/SD calculations, but I still want the count (and percentage) of N/A responses to be displayed within the bars.

      Any ideas on how to do this?

      Notes:
      -I should mention that N/A is not always the same value in each item; sometimes it's 5, other times it's 6, 7, or 9.
      -I did create a merged tab when adding the data to Tableau in order to allow me to display response option labels and values between visualizations with common values but unique response options.
      -I tried recoding N/A responses to "0" in the original data file, but that has created some strange problems with both calculating means and displaying counts of N/A responses. 

       

      Thank you!

       

        • 1. Re: Exclude a value from a calculation, but not from a count.
          Jennifer Pattershall-Geide

          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!

          Jen

          • 2. Re: Exclude a value from a calculation, but not from a count.
            Jessica Schuett

            Hi Jen,

            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...)

            • 3. Re: Exclude a value from a calculation, but not from a count.
              Jennifer Pattershall-Geide

              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?

              • 4. Re: Exclude a value from a calculation, but not from a count.
                Jessica Schuett

                Hmm... I think that might work! Let me go putz around and see what happens. Thank you for the idea!

                • 5. Re: Exclude a value from a calculation, but not from a count.
                  Jonathan Drummey

                  Hi Jessica,

                   

                  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.

                   

                  CASE [QuestionID]

                       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

                  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.

                   

                  Jonathan

                  • 6. Re: Exclude a value from a calculation, but not from a count.
                    Jessica Schuett

                    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

                     

                     

                    Thank you!