I try to compare two expressions in an array on spotfire but they do not give me the same result and I don't understand why from a mathematical point of view.
Sum([OUTS_P] - [OUTS_P2])
Sum([OUTS_P]) - Sum([OUTS_P2])
Do you have an idea in which case these two operations could be different ?
take this example table:
A B
1 3
2 2
3 1
we have these two results:
Sum([A]) - Sum([B]) = Sum(1, 2, 3) - Sum(3, 2, 1) = 6 - 6 = 0
Sum([A] - [B]) = Sum( (1 - 3), (2 - 2), (3 - 1) ) = Sum(-2, 0, 2) = 0
this is what you're expecting, and this will work 100% of the time.
unless, of course, your table resembles this one:
A B
1 3
2
3 1
B:2 is NULL or (Empty). this table results in the expressions being evaluated as:
Sum([A]) - Sum([B]) = Sum(1, 2, 3) - Sum(3, 1) = 6 - 4 = 2
Sum([A] - [B]) = Sum( (1 - 3), (3 - 1) ) = Sum(-2, 2) = 0
the reason is because NULL is non-numeric; it's not possible to evaluate 2 - NULL, and this data is ignored by Sum().
if you want both expressions to always result in the same answer, you can create a calculated column like this for each column you'll be using in Sum():
If([Column] is NULL, 0, [Column])
and then aggregate on this column instead of the original.