While summing up null values the output should be zero but I am getting negative zero and some garbage values. Please help me how to get rid of such values.
for eg :
value + amount = null + null = -2.387E10
Before summing up, check if any of the elements is null. In such case, set 0 as the result.
Try something like:
if(not(isnull(Value) and not(isnull(amount),Value+amount,0)) as Result
Sum(If(Len([Pre-Tax_Value_for_CY])>0,[Pre-Tax_Value_for_CY], 0) + If(Len([Pre-Tax_Value_for_LY])>0,[Pre-Tax_Value_for_LY], 0))
If the amount and value you mentioned are fields, you could simply use RangeSum(), like:
rangesum and isnot null is working fine for null values....but while summation is not working for all the scenarios like :
instead it is giving 1+ -1 = -2.345E10
It could be decimal precision issue. What you see, 1/-1 might be those are actually something with more decimal pionts like:
+/-0.999991211 or so. Load the numeric data and change the number format in the number tab to check the actual value it is being loaded as.
or you can even use FABS() function.
fabs doesn't give me correct results
You may try NumSum(value + amount)
Thanks guys for the replies!!
I resolved it by rounding off the values:
round(value,1) + round(amount,1)
Its working correctly now.
Retrieving data ...