2 Replies Latest reply: May 15, 2014 10:05 AM by Leanna _ RSS

    Calculating with User-Defined Field

    Leanna _

      I have added a numeric user-defined field to manually clear offsetting items on different lines.  It is showing up as Null, and when I try to add it to another field, the result is always Null.  Is there an equivilent of Nz in Monarch to change the Null to zero?

        • Calculating with User-Defined Field
          Data Kruncher

          Hi Leanna,

           

          Yes, as you've found, any time that you include a Null value as part of a calculation, the result is Null too.

           

          So when you know that nulls may exist you can either factor this into the total calculation, or handle every possible null value with a separate calculated field. Either way, the solution always involves this :

          If(IsNull(MyField),0,MyField)[/code]So accumulating fields that each might have a null value would be:

          If(IsNull(FieldA),0,FieldA) + If(IsNull(FieldB),0,FieldB) + If(IsNull(FieldC),0,FieldC)[/code]HTH,

          Kruncher