Set Analysis - Nulls, Multiple Conditions, Ors

[#1]

Hi everyone,

I'm trying 3 things in this set analysis that I am not too comfortable with yet.

My code currently looks like this:

Count({$-<Employee.Manager = {'*'}, User.ID = {'90*'}, User.ID = {'5*'}>} DISTINCT User.ID)

+

Count({$<Employee.Manager = {'Not assigned'}, User.ID = {'90*'}, User.ID = {'5*'}>} DISTINCT User.ID)

I want to Count DISTINCT User.IDs where the Employee.Manager is null, or is Not assigned.  I also only want to select user IDs that begin with 90 or 5.

Note that I tried replacing all null values of Employee.Manager with 'Not assigned', but I think if User.ID doesn't exist in the Employee table, it'll show up as null no matter what.

Thank you!

[#2]

Hi,

Try with this:

Count({$<Employee.Manager = {"=Isnull([Employee.Manager])", 'Not assigned'}, User.ID = {'90*', '5*'} >} DISTINCT User.ID)

Regards,

Ricardo

[#3]

Hi,

     Just similar to the above solution and can also try this

=Count({$ <Manager={ "=len(Manager)=0"  , 'Not Assigned'}, UserID={'5*','9*'}> }DISTINCT UserID)

//Yusuf

[#4 · Accepted solution]

Thank you for the suggestions.

Ended up getting it with:

=Count({$<UserID= {"=Len(Trim(Manager))=0"}>} DISTINCT UserID)

I'm still confused as to why it's ( {<A = {B}>} A) and not ( {<B = {B}>} A)... but it works!

[#5]

I was as confused as you looking at this, but I reasoned out what it's doing.  Glad I found this though.

({<A={B}>} A)

the trick is that B isn't s field but a boolean operation confirming whether to include that A or not.

replace B above with

'is B null' returning true or false.

the set analysis is then if A=TRUE .. distinct A

But this isn't saying that A equals TRUE, but more simply, true - A is a valid A