Only one-to-one

[Anonymous]

Use Case:

Employee	Sales	Region
A	500	Americas
B	600	Europe
B	300	Asia
B	400	Americas
C	200	Americas
C	500	Europe

In my Set analysis statement, I'm interested in Employees who made sales in Americas only. A above, discard B and C.

My set expressions is little complex and Only() doesn't seem to work, is there an alternate ?

Thank you.

[JonnyPoole · Accepted solution]

This will look at only the records with 'Americas' and the employees who have sold to one region

sum( {$<Region={'Americas'},Employee={"=count(distinct Region)=1"}>}  Sales)

[Anonymous]

Thanks Jonathan.

I have another slightly different requirement.

Is it possible via Set Expression to look for Employees who made Sales in Europe and/or Asia but exclude all those who made Sales in America, even if they made in Europe and/or Asia.

[JonnyPoole]

You can using the e() function. I had to augment your sample but here is the result:

sum( {$<Employee=e( {1<Region={'Americas'}>})>} Sales)