topic Re: Minus in expression not working in QlikView

Minus in expression not working

Fri, 14 Apr 2017 05:16:20 GMT

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

Re: Minus in expression not working

Fri, 14 Apr 2017 07:04:46 GMT

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

Please help.

Re: Minus in expression not working

Anil_Babu_Samineni — Fri, 14 Apr 2017 09:29:25 GMT

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

If labels then try [Expression 1] - Expression2

Re: Minus in expression not working

Anonymous — Fri, 14 Apr 2017 09:53:29 GMT

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

Re: Minus in expression not working

Fri, 14 Apr 2017 12:03:19 GMT

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

Re: Minus in expression not working

Anonymous — Fri, 14 Apr 2017 12:38:23 GMT

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)

Re: Minus in expression not working

Fri, 14 Apr 2017 12:56:52 GMT

Ok. Thank you verify much.

Re: Minus in expression not working

Anonymous — Fri, 14 Apr 2017 15:18:21 GMT

I made the same mistake