Weighted Average

[#1]

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!

[joshabbott · Accepted solution]

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)

[marcus_sommer]

Maybe so:

t1:

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

- Marcus

[MarcoWedel]

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

[#5]

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