topic Re: EXCEL Sumproduct Functionality in QlikView

EXCEL Sumproduct Functionality

Tue, 07 Aug 2012 21:07:20 GMT

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?

Course	Score	Weight
Calculus	5	4
Discr. Math	4	3
English Lit.	4	2
Chemistry	4	2
Comp. Sci.	5	3

Thanks very much.

Re: EXCEL Sumproduct Functionality

swuehl — Tue, 07 Aug 2012 21:21:36 GMT

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.

Re: EXCEL Sumproduct Functionality

Wed, 08 Aug 2012 12:44:06 GMT

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

Course	Score	Weight	Weight AVG
Calculus	5	4	5
Discr. Math	4	3	4
English Lit.	4	2	4
Chemistry	4	2	4
Comp. Sci.	5	3	5

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

Re: EXCEL Sumproduct Functionality

swuehl — Wed, 08 Aug 2012 12:46:17 GMT

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)

Re: EXCEL Sumproduct Functionality

Wed, 08 Aug 2012 13:30:20 GMT

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?

Re: EXCEL Sumproduct Functionality

swuehl — Wed, 08 Aug 2012 13:37:50 GMT

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)

Re: EXCEL Sumproduct Functionality

Thu, 23 Aug 2012 20:07:01 GMT

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

Course	Score	Weight	Weight AVG
Calculus	5	4	5
Discr. Math	4	3	4
English Lit.	4	2	4
Chemistry	4	2	4
Comp. Sci.	5	3	5

Re: EXCEL Sumproduct Functionality

swuehl — Fri, 24 Aug 2012 10:02:42 GMT

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