Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
tracycrown
Creator III
Creator III

Aggregate & Average If Condition

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  

2 Solutions

Accepted Solutions
MayilVahanan

Hi @tracycrown ,

PFA.

You need to use intervalMatch concept to achieve your result.

Thanks & Regards, Mayil Vahanan R
Please close the thread by marking correct answer & give likes if you like the post.

View solution in original post

Vegar
MVP
MVP

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.

Vegar_0-1603177158673.png

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.

View solution in original post

4 Replies
MayilVahanan

Hi @tracycrown ,

PFA.

You need to use intervalMatch concept to achieve your result.

Thanks & Regards, Mayil Vahanan R
Please close the thread by marking correct answer & give likes if you like the post.
Vegar
MVP
MVP

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.

Vegar_0-1603177158673.png

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.

tracycrown
Creator III
Creator III
Author

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 

 

 

tracycrown
Creator III
Creator III
Author

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