I have build a report which works fine, except fot the total column.
Report purpose: I have 2 trucks which bring goods to my customers. I need to know how well they are loaded, so that they are not driving around only half full.
Here is the problem:
Expr2: total count(DISTINCT VoyageNr)
Expr3: MaxLoad --> this comes from a seperate table where I my TruckID is unique key
Expr4: sum(DeliveredWeight) / total count(DISTINCT VoyageNr) / MaxLoad
This all works fine, except for the totals colums. For Expr4 the result is wrong.
This is the table:
The total where it says 24,67% should be 46,83%
(16 * 9 + 20 * 14) = 424
198,558 / 424 = 46,83
I've been trying different things with aggregation, but i can't get this result.
any help will greatly appreciated.