1 Reply Latest reply on Oct 6, 2017 12:15 PM by Stephen Rizzo

    Use multiple choice question as a filter - How to set up the data?

    Ásmundur Pálsson

      Hello everyone,

       

      My company has been conducting a customer survey for a local mall in my country. One of the questions the customers receive is a multiple choice question, asking them what stores they visited during their trip to the mall.

       

      Until now we have treated that question as a basic multiple choice question in the data set and just involved it in the list with the other questions. Meaning, when we transform the data in Excel we give the question an unique ID and each choice an unique ID as well. So if someone answers that he went to three stores the data would look like this:

      First column is the identity of the respondent, second is the question ID, third is the string for the question, fourth is the answer, fifth is unique ID for the question+answer, sixth is the string for the answer.

       

      OK.

       

      My question is this: Now the mall wants to use the store multiple choice question as a filter. So they would like to see how someone who went to store xxx answers the other questions about cleanliness and so forth.

       

      How would you guys set up the data so I can use this multiple choice question as a filter?

       

      Thank you in advance.

        • 1. Re: Use multiple choice question as a filter - How to set up the data?
          Stephen Rizzo

          You have several options. You could certainly pivot the data for that question out in the data preparation step (add a field with the numeric response to the multiple choice question and a second field with the text response).

           

          However, an alternative might be to use Level of Detail (LOD) expressions. For example, if your field names are [Respondent ID], [Question ID], and [Answer Text], and the question id for the multiple choice question is 999, the following calculated field would get the answer text for that question at the respondent level:

           

          [Response To Multiple Choice] : {FIXED [Respondent ID] : MAX(IF [Question ID] = 999 THEN [Answer Text] END)}

           

          Among other things, this has the advantage that you can make the question ID for the breakdown a user input. You may need to make some tweaks to the calculated field depending on whether your fields are measures or dimensions, but this should give you a place to start. Also, this assumes that each respondent only submits a single survey.

          1 of 1 people found this helpful