Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Our next Qlik Insider session will cover new key capabilities. Join us August 11th REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
assafmilman
Contributor III
Contributor III

Strange behavior with sum() and aggr()

Hi all,

I'm working on this straight table:

aggr_sum.PNG

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:

  1. sum(A)*B : gave the correct results.
  2. sum(A*B) : gave a result twice as large as that of sum(A)*B
  3. sum(aggr(sum(A*B,siteid))) : gives the correct numbers, but on the wrong lines...

Any idea why only sum(A)*B gives the correct results?

Thank  you.

4 Replies
marcus_sommer
MVP & Luminary
MVP & Luminary

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:

It’s all Aggregations

AGGR...

When should the Aggr() function NOT be used?

- Marcus

assafmilman
Contributor III
Contributor III
Author

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?

marcus_sommer
MVP & Luminary
MVP & Luminary

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

sonikajain
Contributor
Contributor

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!