2 Replies Latest reply: May 15, 2014 10:09 AM by Mauricio _ RSS

    calculated field with (Null)

    Mauricio _

      I have been getting problem by the time that I subtract column A, with less keyword that column B (from External Lookups). I got an (Null) on calculated Field although the column A has a number, i.e. 1 - (Null) = (Null). I need that the calculated Field shows 1 - (Null) = 1 instead of (Null), then I could apply the filter <> 0  too as per summary.

        • calculated field with (Null)
          Olly Bond

          Hello Mauricio and welcome,

           

          You can use the isnull() function to determine whether A or B is Null and adjust accordingly.

           

          To get around this issue, edit your calculated field like this:

           

          if(isnull(B)=1;A;A-B)[/CODE]

           

          If you need to test for A or B being null, then you might need to nest a few more If(Isnull... statements. Another approach is to make a field A2:

           

          if(isnull(A)=1;0;A)[/CODE]

           

          and B2:

           

          if(isnull(B)=1;0;B)[/CODE]

           

          and then define the output field as:

           

          A2-B2[/CODE]

           

          Hope this helps,

           

          Olly