Calculated field doesn't trigger when values are nulls

Question & Answer
1

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?

2

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
3

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
4

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}] )

5

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

1 Like
6

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

7

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.

2 Likes
8

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

9