topic Re: Do not get the difference between sum(measure)Dim vs sum(measureDim) in QlikView

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

bimartingo — Mon, 18 Nov 2013 18:20:08 GMT

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

Re: Do not get the difference between sum(measure)Dim vs sum(measureDim)

swuehl — Mon, 18 Nov 2013 18:38:31 GMT

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?

Re: Do not get the difference between sum(measure)Dim vs sum(measureDim)

jpapador — Mon, 18 Nov 2013 18:42:13 GMT

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.

Re: Do not get the difference between sum(measure)Dim vs sum(measureDim)

bimartingo — Mon, 18 Nov 2013 19:59:29 GMT

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.

Re: Do not get the difference between sum(measure)Dim vs sum(measureDim)

swuehl — Mon, 18 Nov 2013 20:13:45 GMT

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

Re: Do not get the difference between sum(measure)Dim vs sum(measureDim)

rbecher — Mon, 18 Nov 2013 21:34:10 GMT

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

Re: Do not get the difference between sum(measure)Dim vs sum(measureDim)

swuehl — Mon, 18 Nov 2013 21:39:45 GMT

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

Re: Do not get the difference between sum(measure)Dim vs sum(measureDim)

rbecher — Mon, 18 Nov 2013 21:42:04 GMT

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

topic Re: Do not get the difference between sum(measure)*Dim vs sum(measure*Dim) in QlikView

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

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

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

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

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

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

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

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

topic Re: Do not get the difference between sum(measure)Dim vs sum(measureDim) in QlikView

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

Re: Do not get the difference between sum(measure)Dim vs sum(measureDim)

Re: Do not get the difference between sum(measure)Dim vs sum(measureDim)

Re: Do not get the difference between sum(measure)Dim vs sum(measureDim)

Re: Do not get the difference between sum(measure)Dim vs sum(measureDim)

Re: Do not get the difference between sum(measure)Dim vs sum(measureDim)

Re: Do not get the difference between sum(measure)Dim vs sum(measureDim)

Re: Do not get the difference between sum(measure)Dim vs sum(measureDim)