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

# 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?

• ###### Re: maths: columns A - B when B has no value

Hi,

Try without ' '

if(IsNull(ForecastAmt), 0, ForecastAmt)

Kind regards,

Andrei

• ###### Re: maths: columns A - B when B has no value

For Difference column try:

=RangeSum( [ActualAmount], -[ForecastAmt])

• ###### Re: maths: columns A - B when B has no value

Or

[ActualAmount] - Alt([ForecastAmt]), 0)

• ###### Re: maths: columns A - B when B has no value

Hi ,

Thanks,

AS

• ###### Re: maths: columns A - B when B has no value

'-' says that the value is null. any mathematical operation with null gives you null

I suggest you to use Alt or RangeSum or NumSum in those cases

=ActualAmount - Alt(ForecastAmt, 0)

or

=RangeSum(ActualAmount,-ForecastAmt)

or

=NumSum(ActualAmount,-ForecastAmt)

• ###### Re: maths: columns A - B when B has no value

Hi,

NullAsValue ActualAmount, ForecastAmt;

Set NullValue = 0;

Satya

• ###### Re: maths: columns A - B when B has no value

Thanks everyone,

After I did a resident load of the same table (because I still got a '-' no matter which suggestion I tried), then all of the methods suggested worked.

I'm still not sure why it didn't work though.  (I used a Table Box object to see the data)

I would mark all of you as correct if I could.  Thanks so much.