topic Re: Aggregate & Average If Condition in QlikView

Aggregate & Average If Condition

tracycrown — Wed, 25 Nov 2020 16:16:04 GMT

Dear All

Kindly advise how to integrate average and aggregate in If-then condition:

Table A - Grade is OK

Table B - Grade is wrong if using average score ?

Table C - (a) Not sure why I cannot use sum(Lower) and sum(Upper) in expression ?

(b) Wrong Lower and Upper in Grade 5,6 and 7 ?

Thank you, Tracy

Re: Aggregate & Average If Condition

MayilVahanan — Tue, 20 Oct 2020 06:51:53 GMT

Hi @tracycrown ,

PFA.

You need to use intervalMatch concept to achieve your result.

Re: Aggregate & Average If Condition

Vegar — Tue, 20 Oct 2020 07:05:35 GMT

Table C.
You did not use SUM(Lower) nor SUM(Upper) you used (Lower) and (Upper) that is equivalent to Only(Lower) and Only(Upper). In addition you have loaded more than one set of grades into your data model.

Table B

You are comparing to the sum(Grade) which will increase to above the limits of the grade. In addition you are referring to the Grade aggregated on both [Score] and [Term] which will give you trouble. Try adjusting the grade expression to this:

Aggr(If(avg(Score)>= (Lower) and avg(Score) <= (Upper),Only(Grade)),Student, Subject,Lower, Upper)

Note

Your sample did not run because you had reference to two excelfiles. I'm attaching my edited qvw only loaded with the excel you attached to the posting.

Re: Aggregate & Average If Condition

tracycrown — Tue, 20 Oct 2020 07:30:46 GMT

Dear Mr. Vegar

Many thanks for pointing out two different sets of data were used as I must be too tired after trying to solve the problem for many hours. Am I correct to say sum(Score) can be replaced by Score in future?.

Best Regards, Tracy

Re: Aggregate & Average If Condition

tracycrown — Tue, 20 Oct 2020 07:38:38 GMT

Dear Mr Mayilvahanan

Many thanks for introducing the concept of intervalmatch again. In fact, I had tried it before but failed so I dropped it. Now I know where is the error after studying your model answer.

Best Regards, Tracy