Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all,
I'm working on this straight table:
I've 2 fields, A and B. B has the same value for all rows, but A differs and sometimes is null. I wish to calculate sum of A*B for every siteId and weekNum.
I tried doing this with 3 different expressions:
Any idea why only sum(A)*B gives the correct results?
Thank you.
I think your expression should be: sum(A) * only(B). Your second expression aggregated B and led therefore to the wrong results. The third expression had a incorrect syntax and should be rather like: sum(aggr(sum(A)*only(B),siteid)).
But why will you use them? Also why didn't you hide NULL in field A?
To aggeregations and especially with the aggr-function see here:
When should the Aggr() function NOT be used?
- Marcus
I figured that in this specific case
sum(A)*B = sum(A*B), since B is identical for all siteID's.
It's basically just like
1*2+2*2+3*2 = (1+2+3)*2
Does QlikView's sum() work in a different way?
This isn't the point:
F1 F2 F3 sum(F1*F2)
1 - 2 -
1 2 2 2 * (2 + 2) = 8 // and not on row-level 2 * 2 = 4
....
This results because F1 is for both rows identical.
- Marcus
Hey..am trying to replicate and understand the abnormal behaviour. But I am unabel to replicate.
Below is waht I used. Am I missing something.
Set NullInterpret = 'A'
Load * Inline [
A, B, C
1,2,3
2,A,3
3,3,3
4,A,3
5,4,3
]
;
and my output for sum(B*C) is coming correct.
I am getting curious to replicate whats happenign above,
Please help and explain
thanks!