Minus in expression not working

[#1]

We have two expression, each which returns correct number of records. Expression 1 returns 147 records and expression 2 returns 2 records. I want to minus these records and want to display the difference of the two data sets. It should return 145 records, but it is not working. please help. Thanks

Expression 1

(aggr( FirstSortedValue( {<OPENING_DATE = {'<=$(=NUM(As_Of_Date-1))'}>} ACCOUNT_ACCOUNT_NUMBER, ACCOUNT_NUMBER_SEQ), ACCOUNT_NUMBER_SEQ))

Expression2
(aggr(FirstSortedValue({<Num_Date = {'<=$(=NUM(As_Of_Date)-1)>=$(=NUM(As_Of_Date)-[Active Duration])'}>}ACCOUNT_NUMBER, -TRANSACTION_DATE), ELEMENT_ID, ACCOUNT_NUMBER, SECURITY_ID) ) 

Minus Expression 

(aggr( FirstSortedValue( {<OPENING_DATE = {'<=$(=NUM(As_Of_Date-1))'}>} ACCOUNT_ACCOUNT_NUMBER, ACCOUNT_NUMBER_SEQ), ACCOUNT_NUMBER_SEQ))  -
(aggr(FirstSortedValue({<Num_Date = {'<=$(=NUM(As_Of_Date)-1)>=$(=NUM(As_Of_Date)-[Active Duration])'}>}ACCOUNT_NUMBER, -TRANSACTION_DATE), ELEMENT_ID, ACCOUNT_NUMBER, SECURITY_ID) ) 

[#2]

The minus expression returns the 2 records of expression 2. However, it should return 145 records.

Please help.

[Anil_Babu_Samineni]

I am assuming, You are doing Front-end. Then Try it like Column(1) - Column(2)

If labels then try [Expression 1] - Expression2

[Anonymous]

try

rangesum(

(aggr( FirstSortedValue( {<OPENING_DATE = {'<=$(=NUM(As_Of_Date-1))'}>} ACCOUNT_ACCOUNT_NUMBER, ACCOUNT_NUMBER_SEQ), ACCOUNT_NUMBER_SEQ)), 
- (aggr(FirstSortedValue({<Num_Date = {'<=$(=NUM(As_Of_Date)-1)>=$(=NUM(As_Of_Date)-[Active Duration])'}>}ACCOUNT_NUMBER, -TRANSACTION_DATE), ELEMENT_ID, ACCOUNT_NUMBER, SECURITY_ID) )

because null()-x or x-null() is null()

[#5]

Thanks a lot Robin, it works fine now. Can you please throw some light on it how rangesum() is functioning in this case? What does minus sign represent before second expression ?

Thanks

[Anonymous · Accepted solution]

rangesum just sums up every expression separated by comma and handles null() as 0

The -expression is the same as -1*expression.

For further information about null handling have a look here:

NULL handling in QlikView

(btw. please mark the answer as correct... if correct)

[#7]

Ok. Thank you verify much.

[Anonymous]

I made the same mistake