topic Average calculation help? in QlikView

Average calculation help?

Thu, 08 Sep 2011 22:05:40 GMT

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!

Re: Average calculation help?

johnw — Thu, 08 Sep 2011 23:07:12 GMT

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.