topic Re: Average of Average Expression in QlikView

Average of Average Expression

Mon, 01 Dec 2014 04:08:30 GMT

Hi All,

I need to get the average across the 4 student per category highlighted in red.

For now, I can get the average score per student for each category. I am using the following expression:

=(SUM({$(eMPE_Set)}QuestionData)/

COUNT({$(eMPE_Set)} Record_No))/

Max({$(eMPE_Set)}MaxRatingScore)

The MPE expression is a set expression to get a specific exam type, example. English.

1. QuestionData is the score value.

2. Record No is a count of the number of records.

3. MaxRating Score is the maximum score of each questions.

4. The result of the above expression is translate into %.

What am I missing? Can anyone please guide me on this?

Thanks

Chun Wey

Re: Average of Average Expression

tresB — Mon, 01 Dec 2014 05:12:18 GMT

I guess you are missing TOTAL in the aggregation functions. Try like:

=(SUM(Total {$(eMPE_Set)}QuestionData)/

COUNT(Total {$(eMPE_Set)} Record_No))/

Max(Total {$(eMPE_Set)}MaxRatingScore)

If this doesn't help, try to share your sample qvw explaining the expected output there.

Re: Re: Average of Average Expression

Mon, 01 Dec 2014 05:51:37 GMT

Hi,

Attached qvw for your reference. As advised, the TOTAL did not show the correct result.

If you opent the qvw, expected output is the 'Avg Score per category per cohort' line result shown. this will changed according to your cohort chosen. The answer should be same with the image attached in previous thread.

Do let me know if you any other questions.

Thanks

Chun Wey

Re: Average of Average Expression

tresB — Mon, 01 Dec 2014 06:18:51 GMT

Lost in your 'cohort' ! Could you be a bit more specific? chart? expression? selection?

Re: Average of Average Expression

Mon, 01 Dec 2014 06:53:51 GMT

Hi,

Apology. Expected Output below (Please refer to chart).

Khoo Score: Individual student Score

+2 SD: +2 Standard Deviation

-2 SD: -2 Standard Deviation

Avg Score: Average score for all students that are peers of Khoo (inclusive of Khoo result) - which is also known as cohort. In layman term, it is similar to the average score of the whole class where Khoo belongs to.

This cohort is based on a combination of Selections as of below:

Academic Year: 2013

AST: 1

I hope the above clear things up. Let me know if you have any other questions

Re: Average of Average Expression

tresB — Mon, 01 Dec 2014 08:01:40 GMT

Professionalism - 301/43/9 ->77.78% , but you expect 70% . Please explain the numbers(x/y/z) as you want alongwith the logic. Hope his clarification be enough for me.

Re: Re: Average of Average Expression

Mon, 01 Dec 2014 08:53:45 GMT

Hi Tresesco,

That chart output is an illustration, the figure, 70%, is just an incorrect one.

Have attached excel for your reference.

You get 77.8% because u add the raw score and divide by number of records. (In the excel sheet, I had coded in Blue background) => This is an average of all score for professionalism.

Average of Average (In the excel sheet, I had coded in yellow background) => I am trying to get this.

Step 1: I need to get an average of professionalism per student. (I have highlighted them in orange)

In this excel, I have 4 students with each average of 67.9%, 84.26%, 78.70%, 77.78%.

Step 2: I need to average this out: (67.9 + 84.26 + 78.70 + 7.78)/ 4 to get 77.16% (Figure I expected)

Thanks again for your help!

Chun Wey

Re: Average of Average Expression

tresB — Mon, 01 Dec 2014 09:30:27 GMT

Try:

=Avg(Aggr(((SUM({$(eMPE_Set)}QuestionData)/

COUNT({$(eMPE_Set)} Record_No))/

Max({$(eMPE_Set)}MaxRatingScore)

), Category,[Resident Full Name]))

Re: Average of Average Expression

Mon, 01 Dec 2014 09:49:32 GMT

Hi Tresesco,

Yes, it do give the answer i want, thanks!

Question: How to make this Average Score to show the student class that he/she belongs to?

if I don pick any student name, the score and average overlapped which is correct as I have not selected any student names:

If I select a student name, the individual score followed the average score and they overlapped with each other:

Issue: How to make the 'Average score line' not following John score?

I can close this case if the above qns is solved.

Re: Average of Average Expression

tresB — Mon, 01 Dec 2014 10:03:14 GMT

Create a new variable:

Variable1 -> [Resident Full Name],QuestionType={'MPE'},IsValue={1},RatingID-={''}>

Then expression:

=Avg({<[Resident Full Name]>}Aggr(((SUM({$(Variable1)}QuestionData)/
COUNT({$(Variable1)} Record_No))/
Max({$(Variable1)}MaxRatingScore)
), Category,[Resident Full Name]))

Re: Average of Average Expression

Tue, 02 Dec 2014 01:23:06 GMT

Thanks Tresesco! You're good!!