7 Replies Latest reply: Nov 12, 2017 5:52 PM by Rachel Zhong RSS

    How to select some contents from a column and calculate?

    Rachel Zhong

      Hi dear all, I am trying to help my company build a survey results analysis and now I have some problems.

       

      I have a table below(see attachment)

      And I would like to generate a table below:

      Capture.PNG

      Now I was stocked at how to achieve calculating mean (Mean Q1 to Mean Q5), as an measurement. I tried to use do loop function at the expression filed, but it didn't work. Here is what I have tried so far:

       

      Set Q1mean= 0;

      Set Q2mean=0;

      Set Q3mean=0;

      set Q4mean=0;

      set Q5mean=0;

      Set a=1;

      Do while ([Custom workshop.question]='Q1'or [Custom workshop.question]='Q2' or [Custom workshop.question]='Q3' or [Custom workshop.question]='Q4' or [Custom workshop.question]='Q5')

         Q1mean=[Custom workshop.question value]+Q1mean;

         Q2mean=[Custom workshop.question value]+Q2mean;

         Q3mean=[Custom workshop.question value]+Q3mean;

         Q4mean=[Custom workshop.question value]+Q4mean;

         Q5mean=[Custom workshop.question value]+Q5mean;

      Let a=a+1;

      Loop

       

      return (Q1mean+Q2mean+Q3mean+Q4mean+Q5mean)/count(distinct Respondent_No)

       

      But it did't work. Anyone could give some suggestions are really appreciated.