Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
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.

Tags (2)
7 Replies
MVP
MVP

Re: EXCEL Sumproduct Functionality

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

Re: EXCEL Sumproduct Functionality

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
MVP

Re: EXCEL Sumproduct Functionality

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

Re: EXCEL Sumproduct Functionality

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
MVP

Re: EXCEL Sumproduct Functionality

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

Re: EXCEL Sumproduct Functionality

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
MVP

Re: EXCEL Sumproduct Functionality

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

Community Browser