Qlik Community

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Qlik Insider: SaaS capabilities for Data Integration and Analytics, May 25th! Register Now
cancel
Showing results for 
Search instead for 
Did you mean: 
Ambers_sp
Contributor
Contributor

Count Unique Match and Exclude in the same set analysis

Hi all,

I am needing to count distinct account_num but from three different tables but I also need to exclude the count if they appear in another table. I have been using P() for those that match up, but I am having issues on count distinct excluding those in common.

For example: table1 = new customer, table2 = existing customer, table3 = lost customers. 

count({<Table3_account_num=P(table1_account_num), table3_account_num=-P(table2_account_num)>}distinct table3_account_num)

I put a '-' before the P because E() was returning zero and thought at a strech this might work, which it doesn't. 

I should mention all my tables are separate, they are linked by any key. 

essential I want to say, count distinct all account_nums that are in table 3 that are also in table1, but if an account_num is in table 2 then ignore it from the count. 

 

Any help would be great,

Labels (1)
2 Replies
rubenmarin

Hi, if you use the same field two times in a set analysis only the last is used. You can try with:

count({<Table3_account_num-=P(table1_account_num)+P(table2_account_num)>}distinct table3_account_num)

Ambers_sp
Contributor
Contributor
Author

Unfortunately that doesn't work