# QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Qlik Highlights 2020 Giveaway! Watch, reply and have a chance to win a \$200 Amazon Gift Card! Watch Video
cancel
Showing results for
Did you mean:
Creator

Hi All,

I just can't get my head around what's WRONG with simple subtraction. I am loading from excel. I tried num(A)-num(B) as C but still the the same wrong subtraction is coming. While I am checking in excel, it should be 530 million. Please help. The script is simple.

Load A, B, A-B as C from ....

The sum of A and B is correct but the difference is not coming correct.

 EntryDesc A B A-B as C Actuals 967,305,225.24 436,323,183.98 409,016,914.02 Wrong Subtraction with load Actuals 967,305,225.24 436,323,183.98 530,982,041.26 Correct one should be

Thanks

SAK

1 Solution

Accepted Solutions

If either A or B is null, then the sum of A-B is null, even if the other field has a value.You can use something like

RangeSum(A, -B)

or

Alt(A, 0) - Alt(B, 0)

which will ensure that the null value is treated as a 0.

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

Try like this

Test:

LOAD * , A-B as C ;

A,

B

FROM

(ooxml, embedded labels, table is Sheet1);

OR

Test:

A,

B,

A-B as C

FROM

(ooxml, embedded labels, table is Sheet1);

If either A or B is null, then the sum of A-B is null, even if the other field has a value.You can use something like

RangeSum(A, -B)

or

Alt(A, 0) - Alt(B, 0)

which will ensure that the null value is treated as a 0.

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Champion II

please post a sample app that demonstrates the problem

Creator

"RangeSum(A, -B)"  worked.

Creator

Both working fine. Thanks a ton.

RangeSum(A, -B)

or

Alt(A, 0) - Alt(B, 0)

Master III

Tags