Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all,
We have a problem which is while trying to sum values. If all values has some content like decimal, negative or posiive there is no problem. But when if any null value involved to operation result will automatically will be 0.
I know there is plenty of questions about that issue in site but none of them helped me anyway.
Any idea about that? below you can see the screen shot explanation.
this is one transaction
And the other one is normally null but it is reflecting as 0.
When these two combined value will be 0
here the result when I added these two transaction and see the total result will be like below
if([agreecurr]='USD',
sum(sumagree),
sum(sumbase)
)
Expression like above for Test Total measure. First screen shot has sumagree and we can see the result but second ss it has no defition as USD so it will try to get sumbase column but it is null also.
I am confused why the total will set as 0 if you asist me that would be perfect.
Summing null values should not be a problem. I would suggest checking that the values are actually null, rather than 'NULL' or something similar. If your sum contains a formula (for example, sum(Field1+Field2)) then null values can impact the result because Null + 1 = Null, but you seem to be using a straight Sum(Field). I would also suggest confirming that your if() is evaluating correctly - more typically the correct way to write these is
Sum(If( Condition, Field1, Field2)) rather than If(Condition,Sum(Field1),Sum(Field2))
It's not the sum() else your condition which isn't unique for all values respectively didn't exists on the total-level. The totals work only with really simple sum/count like you expect - as far as your expressions are quotes and/or containing conditions the may - depending from the data-model - work or not.
A quite common approach in such scenarios is to use something like this:
sum(aggr(
if([agreecurr]='USD', sum(sumagree), sum(sumbase)),
[Year Month]))
and depending on your object you may need more as one dimension within the aggr().
- Marcus