Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Live chat with experts, bring your API Integration questions. June 15th, 10 AM ET. REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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?

1 Solution

Accepted Solutions
amit_saini
Master III
Master III

Hi ,

Please see the attachment.

Thanks,

AS

View solution in original post

7 Replies
crusader_
Partner
Partner

Hi,

Try without ' '

if(IsNull(ForecastAmt), 0, ForecastAmt)

Kind regards,

Andrei

tresesco
MVP
MVP

For Difference column try:

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

jonathandienst

Or

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

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
amit_saini
Master III
Master III

Hi ,

Please see the attachment.

Thanks,

AS

View solution in original post

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)


satyadev_j
Specialist
Specialist

Hi,

Try adding below two lines before your load script,

NullAsValue ActualAmount, ForecastAmt;

Set NullValue = 0;

Satya

Not applicable
Author

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.