4 Replies Latest reply on Jul 11, 2018 1:18 AM by lei.chen.0

    Combining the results of 2 datasheets

    Rod A

      Hi,

       

      I have 2 data sheets showing 2 survey questions.

      q4.jpg

       

      q5.jpg

      They are similar questions and I want to combine them. How do I do this?

       

      Another thing, is I need to filter both questions with those who picked a 7 or higher from Q3. Q3 is in  "Question ID" that is already filtered by whatever question I am currently displaying so I'm stuck. I don't know if I have to reshape the data again or if there is a workaround.

        • 1. Re: Combining the results of 2 datasheets
          lei.chen.0

          Hello Rod,

           

          Rod A wrote:

           

          They are similar questions and I want to combine them. How do I do this?

          There are different ways of "combining" them.

          So your requirements are important.

           

          1. Simply add the two worksheets into one dashboard
          2. Simply list the two questions in one worksheet instead of two, the results of the two questions are aggregated individually
          3. Merge the results of the two questions

           

          I noticed that "BMOTracking Extract (2)" is the duplication of "BMOTracking Extract".

           

          It seems that they are exactly the same data source, except for that in "BMOTracking Extract", there is a data source filter selecting only a couple of "Question ID"s.

           

          What's the reason for using two data sources?

           

          Rod A wrote:

           

          Another thing, is I need to filter both questions with those who picked a 7 or higher from Q3. Q3 is in "Question ID" that is already filtered by whatever question I am currently displaying so I'm stuck. I don't know if I have to reshape the data again or if there is a workaround.

           

          Basically, I suppose this could be achieved by a calculated field.

           

          I suppose [Respid] is the unique key for a response, the basic logic is,

          1. Find the [Respid]'s, in which "a 7 or higher from Q3" is picked.
          2. Only include these [Respid]'s for analysis.

           

          So what does "picked a 7 or higher from Q3" in your data?

          I found "final_q_3 : I did not understand some of the questions (final_q)" and "q3 : q3" in [Question ID].

           

           

          Regards

          Lei

          • 2. Re: Combining the results of 2 datasheets
            Rod A

            It's point #3, merging the results of the 2 questions. I want to show one bar chart combining the results of the 2 questions. So if Q4's '$0 annual fee' total is 10 and Q5's '$0 annual fee' total is 15, I would like to combine them as 25 but the percentage is based on the Q3 (q3:q3) total of 7 or more rating. Q3 is a 10-point scale and the base for Q4 and Q5 are only those who rated 7 or more @ Q3.

             

            I did see that it has 2 data sources. I just created a mini prepackaged version of my working file and something happened along the way.

             

            The variable names are copied straight from the survey data export and it displays it that way. Q3 is 'q3 : q3'.

            • 3. Re: Combining the results of 2 datasheets
              Rod A

              Lei Chen

               

              I tried the calculated route for the filter but the Q4 percentages are still calculated using the total base (1505) instead of the reduced base (1189).

               

              q3filter.jpg

              • 4. Re: Combining the results of 2 datasheets
                lei.chen.0

                Hello Rod,

                 

                Fairly sorry for my laaate reply!

                 

                Please try this filter.

                 

                Regards

                Lei