count(DISTINCT (x)) + Set analysis + if

thanhphongle — Wed, 22 Nov 2017 16:53:55 GMT

Hello Community

this is my database:

contract_no	contract_payments_perc	contract_term_type
1	3,11%	0
1	2,40%	1
1	1,5%	2
2	2,4%	0
2	1,7%	1
2	1,0%	2
3	2,8%	0
3	2,8%	1
3	2,8%	2

What I want to do is

QV should count DISTINCT each contract_no where contract_payments_perc is equal to the other contract_payments_perc position inside the same contract_no.

Example:

1. Look up for contract_no = 1 AND contract_term_type = 0 AND the referring contract_payments_perc = 3,11%

2. Compare contract_payments_perc = 3,11 % with contract_payment_perc where contract_term_type = 1 AND 2 (2,4%, 1,5%)

3. if its equal, count distinct contract_no

4. in this case, it is not equal

5. result count(DISTINCT contract_no) = 0

I tried this

count(DISTINCT if( sum({$<contract_term_type = {'0'}> contract_payments_perc) = sum({$<contract_term_type = {'>0'}> contract_payments_perc), contract_no ))

Unfortunetly is does not work.

Hope someone can help me out here

topic count(DISTINCT (x)) + Set analysis + if in QlikView

count(DISTINCT (x)) + Set analysis + if