count and not contains

[ebrahimaljafri]

hi Qlikers

i have created below inline table in order to execute below logic at front end using set analysis  :

(#distinct benefeciaryTransactionLog.BenefeciaryID where benefeciaryTransactionLog.TransactionType = 2)/

(#distinct benefeciaryTransactionLog.BenefeciaryID where benefeciaryTransactionLog.TransactionType in (1,7,8) and not in (2))

TT:

LOAD * Inline [

TRANSACTION_TYPE,BeneficiaryID

1,1

1,2

2,3

2,2

7,4

8,5

2,8

1,8

7,8

1,9

2,10

2,11

7,11

1,12

2,12

7,12

8,12

1,13

7,13

8,13

];

please help

note the actual data is more than 65 m and i am using qlik sense

[sunny_talwar]

Based on the data provided above, what is the expected output? Also, it would be nice to know the expected output for numerator and denominator separately.

[MarioCenteno]

You could explain your goal a bit more, if possible share screenshots or expressions

[ebrahimaljafri]

hi ,

actually i want to count BeneficiaryID in TRANSACTION_TYPE {1,7,8} and not in TRANSACTION_TYPE {2}

this is set analysis i am using but it count BeneficiaryID even if it exist in TRANSACTION_TYPE {2} where it must not

Count({<TRANSACTION_TYPE={1,7,8},TRANSACTION_TYPE-={2}>}BeneficiaryID)

and other set analysis but is not working too

Count({<TRANSACTION_TYPE={1,7,8}>}{<TRANSACTION_TYPE={'<>2'}>}BeneficiaryID)

[Thiago_Justen_]

This expression will works:

Count({<TRANSACTION_TYPE={1,7,8},TRANSACTION_TYPE=-{2}>}BeneficiaryID)

[tresesco · Accepted solution]

May be this?

=Count(DISTINCT {<TRANSACTION_TYPE={1,7,8}>-<BeneficiaryID=p({<TRANSACTION_TYPE={2}>})>}BeneficiaryID)

[DavidŠtorek]

Hi,

"count BeneficiaryID in TRANSACTION_TYPE {1,7,8} and not in TRANSACTION_TYPE {2}". This is a little overcomplicated, if I am not misunderstanding something. Because the resul of this will be count of all except 2.

In this case you should use expression as following

Count({<TRANSACTION_TYPE-={2}>}BeneficiaryID)

Hope this will help you