topic Re: maths: columns A - B when B has no value in QlikView

maths: columns A - B when B has no value

Fri, 20 Jun 2014 06:09:39 GMT

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

crusader_ — Fri, 20 Jun 2014 06:14:50 GMT

Hi,

Try without ' '

if(IsNull(ForecastAmt), 0, ForecastAmt)

Kind regards,

Andrei

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

tresB — Fri, 20 Jun 2014 06:15:31 GMT

For Difference column try:

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

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

jonathandienst — Fri, 20 Jun 2014 06:23:42 GMT

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

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

amit_saini — Fri, 20 Jun 2014 06:44:57 GMT

Hi ,

Please see the attachment.

Thanks,

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

CELAMBARASAN — Fri, 20 Jun 2014 06:51:47 GMT

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

=RangeSum(ActualAmount,-ForecastAmt)

=NumSum(ActualAmount,-ForecastAmt)

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

Anonymous — Fri, 20 Jun 2014 06:51:57 GMT

Hi,

Try adding below two lines before your load script,

NullAsValue ActualAmount, ForecastAmt;

Set NullValue = 0;

Satya

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

Fri, 20 Jun 2014 07:24:53 GMT

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.