4 Replies Latest reply on Apr 24, 2015 12:18 PM by gremlin9297

Calculation that includes empty Fields

Title

Calculation that includes empty Fields

Post

I have returned!

New dilemma, I have a calculation:

If (

A > 0 And

B > 0 And

C > 0 ;

Sum (
A;

B;

C;

))

Problem is that field C was being used for previous years, it has been replaced with B going forward. So I need the calculation to work regardless if the field is empty or not. Any help would be great. THANKS!

• 1. Re: Calculation that includes empty Fields

Sum(
If (A>0;A);
If(B>0;B);
If(C>0;C)
)

also this allows for negative numbers

Sum(
If (not IsEmpty A; A);
If(not IsEmpty A;B);
If(not IsEmpty A;C)
)

• 2. Re: Calculation that includes empty Fields

That solved the empty field but the calculation is only suppose to run if all all the fields were greater than zero. The calculation your provided runs as long as at least one of the fields is greater than zero.  In other words it should only be calculating the fields if A and B or A and C are greater than zero.

• 3. Re: Calculation that includes empty Fields

Let(
[
@ab = If(not IsEmpty(B);A+B;"");
@ac = If(not IsEmpty(C);A+C;"")];
Case(
A>0 and @ab;
A+B;
A>0 and @ac;
A+C;
""
)
)

This assumes that there is never an B and C on a record at a given time. It will be B or C.

• 4. Re: Calculation that includes empty Fields

That was what I was looking for, though I did have to modify it a bit. The calculation you provided made the calculation regardless if the record was greater than zero. The calculation had to make sure both fields were greater than zero or else ignore it.

Let ( [

bb=If(not IsEmpty(B);B;"");
cc=If(not IsEmpty(C);C;"")];

Case(
A>0 and bb>0;A+B;
A>0 and cc>0;A+C))

thanks again for the help. Definitely got me through this problem :)