7 Replies Latest reply: Jun 20, 2014 3:24 AM by Tricia Chan RSS

    maths: columns A - B when B has no value

      I have search various use of Null values but none can fix my issue.  Hope someone knows this:

       

      I have:

      column A from tblActuals

      column B from tblForecasts

       

      when I do a join, I get this (they are correct except when there is a "nothing"):

      ActualAmount     ForecastAmt    Difference

      100                       120               -20

      160                          -                 -          <--I want ForecastAmt to show 0, and Difference to show 160

      103                       150               -47

       

      I've tried:

      if(Len(ForecastAmt) > 0, len(ForecastAmt), '0') --- but I still get a '-'

      if(IsNull(ForecastAmt), '0', ForecastAmt)          --- I also get a '-'

       

      Does anyone know what's wrong with it?