sum() does not take multiple instances of same value into account

[#1]

Hello,

I have the following problem.

[referenceduties]:

LOAD * INLINE [

    ReferenceDutyID, RefDuty

    1, 100

    2, 200

];

[Sales]:

LOAD * INLINE [

    ID, Model, Selling, ReferenceDutyID

    1, X, 1000, 1

    2, X, 1000, 1

    3, Y, 1500, 2

];

When I calculate the average selling price per RefDuty I get an incorrect result:

sum(Selling) / sum(RefDuty) = 11,7

QV calculates 3500 / 300 = 11,7 (where 300 = 100 + 0 + 200)

expected behavior: average = 3500 / 400 = 8,75  (where 400 = 100 + 100 + 200)

I guess I'm doing something wrong as the RefDuty 100 is only counted once, even though in the sales table it occurs twice (ID 1 and 2)

Any suggestions?

Thanks

Peter

[alexandros17]

Do the same reloading the following tables:

[Sales]:

LOAD * INLINE [

    ID, Model, Selling, ReferenceDutyID

    1, X, 1000, 1

    2, X, 1000, 1

    3, Y, 1500, 2

];

left join

LOAD * INLINE [

    ReferenceDutyID, RefDuty

    1, 100

    2, 200

];

Let me know

[rbecher · Accepted solution]

Hi Peter,

here we go:

=sum(Selling) / sum(aggr(sum(RefDuty), ID))

- Ralf

[rbecher]

Doesn't work since you have to aggregte over ID, not ReferenceDutyID..

[#5]

Hello Ralf,

That solved my problem. Allessandro Saccone's solution also worked, but it is not a generic answer as it is not always possible to do a "left join".

Thanks

Peter