Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Hi @tracycrown ,
PFA.
You need to use intervalMatch concept to achieve your result.
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.
Hi @tracycrown ,
PFA.
You need to use intervalMatch concept to achieve your result.
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.
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
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