Do not get the difference between sum(measure)*Dim vs sum(measure*Dim)

[bimartingo]

I have a dimension like day and for every day a factor with a fixed value for the day.

I want to sum a measure for different products multiplied by factor of each day. So I'll get a weighted sum.

I mounted a chart of type table with dimension day and factor and two expressions:

expA : sum(measure * factor)

expB : sum(measure) * factor

mathematically thinking both values should give the same result, but it doesn't happen.

Why ?

day	factor	sum(measure)	sum(measure)*factor	sum(measure*factor)
		552	276	552
01oct2012	0.5	552	276	552

the expected answer is 276

[swuehl]

I can't reproduce your issue.

The answer to your issue is probably depending on your data model or the data itself.

Could you upload a small sample application that demonstrates your issue?

[jpapador]

Without a bit more data I cannot be sure.  But the difference would be that is Sums all the measures and then multiplies is by the factor in the Sum(Measure)*factor example.  In the Sum(Measure*Factor) example it will multiply each individual line by the associated factor and then sum each lines result. 

[bimartingo]

neither can I with a simplified sample data. As The orignal data is too complex to load into an example, i cannot attach it here.

Anyway, thanks John Witherspoon i got a solution, through sum(aggr(sum(measure),day) * factor) , even for my strange data.

As i understood aggr(sum(measure),day) sums, for each day, the value of measure and then multiplies by factor, and the overall total becomes the sum of the sums for all rows. and that is what i wanted.

[swuehl]

Yes,

this might give you the result you expect.

But I would still strongly suggest that you try to understand why you see this behaviour, today or in the near future.

The risk is quite high that you (or the next developer editing your document) run into similar problems or even more tricky ones.

Regards,

Stefan

[rbecher]

Maybe it's related to some NULL values ...

[swuehl]

Yes, I also thought about NULLs, but wouldn't we suspect sum(measure*factor) to be smaller than sum(measure)*factor then?

[rbecher]

Either measure or factor could be null (or negative)...