DSXchange

hi i am trying to calculate the average price via the aggregator. the scenario is as follows. i have a prodid, amount and qty. i am multiplying the price against quantity in the transform to obtian the total value. the quantity can be any number i.e 1 - 100. when i perform the average aggregation in the aggregator it seems to only total up the number of rows and not the exact quantity.

example. prodid amount quantity
1 100 1
1 100 10

the result i am getting is 1100 / 2. the result i require is 1100 / 11. suggestions most welcome.

thank you for your assistance.

The 'average' calculation the Aggregator does is based on the average value of a field over the number of rows passed through the stage. Which you discovered.

If you need it based on your two fields, I'd suggest passing two data fields through the agg - first the two multiplied together and secondly the count - grouping on the key field (as you are doing now) and summing on those two fields. Then, in a transformer after the Aggregator, divide the two results to get your average.

thanks. that has worked.

Excellent!