Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

How to select some contents from a column and calculate?

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.

7 Replies
mdmukramali
Specialist III
Specialist III

Dear Rachel Zhong,

Can you share what output are you expecting from the Excel Data?

Thanks,

Mukram.

Anonymous
Not applicable
Author

The RangeAvg() function could be what you are after - here is the link to its online manual entry which has some examples :

RangeAvg - script and chart function ‒ Qlik Sense

Anonymous
Not applicable
Author

Hi Mohammed,

Thanks for your reply, I have attached the table sample I expect to achieve.

Rachel

Anonymous
Not applicable
Author

IHi Bill, thanks for your reply. Could you explain more clearly?

Anonymous
Not applicable
Author

Ignore the RangeAvg() function I wrongly guessed about your input data.

Having seen your xlsx and assuming it is your input data then maybe something like the attached qvf.

Anonymous
Not applicable
Author

Or if you are after the overall average of the discrete averages then maybe something like this qvf

Anonymous
Not applicable
Author

Thank you Bill, it works. I made some changes and got the results. !