Count Unique Match and Exclude in the same set analysis

[Ambers_sp]

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,

[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]

Unfortunately that doesn't work