topic Re: Weighted Average in QlikView

Weighted Average

Fri, 22 Aug 2014 14:35:30 GMT

Hi Community,

I need help on calculating a weighted average. A simple version of my problem and the accordant data look as follows:

machine weight time

A 60 4

A 80 9

A 90 8

A 1000 4

B 60 3

B 90 5

B 90 9

B 100 2

B 100 3

What I aim at is to calculate the weighted average of time for each of the machines (A & B) with the weight as the weighting factor.

Can anybody help me on this?

Thanks in advance!

Re: Weighted Average

joshabbott — Fri, 22 Aug 2014 14:41:33 GMT

So the weight for a machine is out of the sum of all weight for that machine right:

sum(TOTAL <machine> weight) - For A this should give you 1230

So to get a percent, I would think you would need to divide the weight for the row into the total:

sum(weight) / sum(TOTAL <machine> weight)

Then, would you multiply this by the time?

(sum(weight) / sum(TOTAL <machine> weight)) * sum(Time)

Re: Weighted Average

marcus_sommer — Fri, 22 Aug 2014 14:44:10 GMT

Maybe so:

t1:

Load machine, sum(weight*time) / count(machine) as avg Resident xyz Group by machine;

- Marcus

Re: Weighted Average

MarcoWedel — Fri, 22 Aug 2014 14:51:48 GMT

Hi,

one solution:

table1:
LOAD machine,
    weight,
    Interval(time)as time
INLINE [
    machine, weight, time
    A, 60, 4
    A, 80, 9
    A, 90, 8
    A, 1000, 4
    B, 60, 3
    B, 90, 5
    B, 90, 9
    B, 100, 2
    B, 100, 3
];

table2:
LOAD machine,
    Interval(sum(weight*time)/sum(weight)) as average
Resident table1
Group By machine;

hope this helps

regards

Marco

Re: Weighted Average

Fri, 22 Aug 2014 17:12:27 GMT

Thanks a lot. This helped me already... 😉