4 Replies Latest reply: Feb 17, 2014 1:05 PM by Srikanth P RSS

    Sum(A) + Sum(B) vs. Sum(A + B)

    Dirk Jonker

      We have a table consisting of:

       

      RowId - a unique identifier for each row

      A - any real number

      B - any real number

       

      There are a couple of ways to sum A and B together:

       

      • Sum(A) + Sum(B)
      • Sum(A + B)
      • Sum(Aggr(A + B, RowId))

       

      Sum(A + B) is equivalent to Sum(Aggr(A + B, RowId))

       

      When A or B can be NULL, Sum(A + B) is not equivalent to Sum(A) + Sum(B).

       

      Why? Because any calculation involving NULL will yield NULL!

       

      To take care of this, you can alter the field on load:

      IF(IsNull(A), 0, A) AS A

       

      Would there be any situations in which you need a metric to be NULL? If so, is there a way to treat NULL as 0 in Sum(A + B)?