1 Reply Latest reply on Jul 18, 2016 6:21 PM by swaroop.gantela

    Adding text field in column to represent 0 survey responses

    Ariel Rosen



      This is probably a quick and easy trick but I can't figure it out. I have survey data I am using and one of the fields, "Never Find" had 0 survey responses, so it does not show up in my graph. I would like to include this response in my visual, just to show that it was an option to choose in the survey (although no one chose it). I've tried to do a calculated field but am having trouble.


      I've attached my workbook. The one I need help with is the "HELP" worksheet, in the "Answer" column, I would like a "Never Find," with a 0% to show under "Rarely Find."


      Any help is much appreciated!




        • 1. Re: Adding text field in column to represent 0 survey responses


          There likely is an easier way to do this, but maybe this can be a first step for you.


          First I joined the datasource to itself, so as to get every combination of [Answer]s.

          Not every combination is pertinent, so the next step is to filter down the [Answer]s.

          I wanted to keep all [Answer]s that belonged to the same group (0/1, Excellent/Very Good ..., etc.)

          so I made a Grouping Calcuation for [Answer] and it's duplilcate [Answer ...$1] :

          IF [Answer]="0" OR [Answer]="1" THEN "BenchMark"

          ELSEIF [Answer]="1: Yes" OR [Answer]="2: No" THEN "Read"



          I then filtered to keep the [Answer]s that belonged to the same group.

          I think this step allowed for the retention of "Never Find" in visualization.


          I'm not sure if the TableCalc for calculating the Percentage was working in this setup,

          so I made my own calculations:


          This may have been overdoing it, but I used a Level of Detail calculation to

          count the number of respondents per the duplicate [Answer ...$1]:

          MAX({ FIXED [Question],[Answer ('SM Usage$'1)]:COUNTD(IF [Answer]=[Answer ('SM Usage$'1)] THEN [Respondent ID] END)})


          which says, if there is an answer, return the RespID, then countd these RespID and Fix that number for each [Question] and [Answer...$1].

          Later steps will need this be aggregated, so I took its MAX.


          Then I Window_Summed the COUNTDResp for each question, and then got the percentage: