Announcements
Qlik Connect 2025: 3 days of full immersion in data, analytics, and AI. May 13-15 | Orlando, FL: Learn More
cancel
Showing results for
Did you mean:
Not applicable

## EXCEL Sumproduct Functionality

Guys,

I'm trying to use Qlikview to solve a weighted average problem. As the example below,

I would like to get an average GPA of (5*4+4*3+4*2+4*2+5*3)/(4+3+2+2+3)=4.5. How can i do it in QlikView?

CourseScoreWeightWeight AVG
Calculus54
Discr. Math43
English Lit.42
Chemistry42
Comp. Sci.53

Thanks very much.

7 Replies
MVP

Try using

=sum(Weight*Score) / sum(Weight)

as expression in a table chart with dimension Course.

assuming that you don't need to calculate your Scores / Weights per Course.

Not applicable
Author

Thanks, swuehl. I tried it, and it still give me

CourseScoreWeightWeight AVG
Calculus545
Discr. Math434
English Lit.424
Chemistry424
Comp. Sci.535

do you know how can i use sum function to get a total of 22?

MVP

You should see your average GPA of 4.5 if you enable the total in expression tab, using expression total.

edit:

Ah, if you maybe want to see the weighted average in every row, use

=sum(total Score*Weight)/sum(total Weight)

Not applicable
Author

Thanks, it works. What if 'course' is a group, there are multiple choice, problem solving under each of the courses.

Let's say there are multiple choice with a socre 5, weight 4 and problem solving a score 5 weight4. Then after some calculation, the course socre is 5. if i use sum(total, then it will be 10, not 5 in the table. Do you know how can i solve this?

MVP

Not sure if I can follow your example. But you can add a field list to the TOTAL qualifier, like

=sum(total<Course> Score*Weight)/sum(total<Course> Weight)

Not applicable
Author

It gives me the same thing as this table. Thanks very much.

CourseScoreWeightWeight AVG
Calculus545
Discr. Math434
English Lit.424
Chemistry424
Comp. Sci.535
MVP

That's probably not what you want? Could you post some sample lines of data together with your expected outcome?

Community Browser