maths: columns A - B when B has no value

[#1]

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?

[crusader_]

Hi,

Try without ' '

if(IsNull(ForecastAmt), 0, ForecastAmt)

Kind regards,

Andrei

[tresesco]

For Difference column try:

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

[jonathandienst]

Or

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

[amit_saini · Accepted solution]

Hi ,

Please see the attachment.

Thanks,

AS

[CELAMBARASAN]

'-' 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)

[Anonymous]

Hi,

Try adding below two lines before your load script,

NullAsValue ActualAmount, ForecastAmt;

Set NullValue = 0;

Satya

[#8]

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.