Calculated field doesn't trigger when values are nulls

I have a calculated field:

difference(sum(amount), [{fiscal_year} ASC],-1,[{ab},{ac},{ae}] )

When the amount is null, the above calculated field doesn’t display anything.

Does anyone know how to fix this?

The issue is that anything minus null returns null. That’s why it’s not displaying anything. Do you want to treat the null as a 0 instead?

Can you try to use the following as the first argument in your difference function?
sum(ifelse(isNull(amount), 0, amount))

1 Like

Yes, I do agree with @David_Wong . Better to convert Null to 0 and do the calculation in this case @gopinathpc .

Regards - Sanjeeb

1 Like

Thanks for the tip.
However I still don’t get any values.
I updated my calculated field as:

difference(sum(ifelse(isNull(amount), 0, amount)), [{fiscal_year} ASC],-1,[{ab},{ac},{ae}] )

Does it not display anything in all rows or in some rows only?

1 Like

Only in a few rows. The value seems to be null and not getting updated to zero.

Did you check the result of the sum function in a separate visual to see if it gives you null values? That would tell us if the issue is with the sum or the difference.


Hi @gopinathpc

We have not heard back from you regarding your question. We would still like to help. If we do not hear back in the next 3 days, we will archive the question