Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Join us in NYC Sept 4th for Qlik's AI Reality Tour! Register Now
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Average calculation help?

Hi,

I'm not sure how to create an expression like so:

On the top left table/chart I get an Average of 92%-Avg([Actual Value]), but this is not working it out as expected.

It should be 91%. Worked out as follows : Per Subject: (Actual Value * Number of Respondants) / Total Number of respondants ie.

(0.95*135)/785 = 91.% (Biological Sciences)

Then doing an average of all of these calculations for the Question...In this case, Question 1.

I've illustrated this in the attached excel sheet.

I basically want to be able to create a Radar chart, with Questions and these averages.

Any ideas appreciated!

1 Solution

Accepted Solutions
johnw
Champion III
Champion III

I think you have a spreadsheet error.  You're calculating 9.1062%, not the desired 91.062%.  It's hard to see the error because you have exactly 10 records, so it's only off a decimal place instead of messing up any digits.  I believe you should be doing a sum, not an average.  You've already handled the average (and made it a weighted average) by dividing by 785.

So what I think you want is this:

sum(aggr([Actual value]*[Number of Respondents],[Question number],[Institution],[Subject],[Level]))/sum([Number of Respondents])

It produces 91.062%, in any case.

View solution in original post

1 Reply
johnw
Champion III
Champion III

I think you have a spreadsheet error.  You're calculating 9.1062%, not the desired 91.062%.  It's hard to see the error because you have exactly 10 records, so it's only off a decimal place instead of messing up any digits.  I believe you should be doing a sum, not an average.  You've already handled the average (and made it a weighted average) by dividing by 785.

So what I think you want is this:

sum(aggr([Actual value]*[Number of Respondents],[Question number],[Institution],[Subject],[Level]))/sum([Number of Respondents])

It produces 91.062%, in any case.