Set Analysis include Null Values only

[ivandrago]

Hi,

I have the attached Document, I want to Count the number of Id's where Status = Active and CompletedDate = NULL

Thanks

[eliko_il]

Hi,

Instead of  -  count( {$<[Status]={'Active'} >} [ID] ) 

change the expression to -  sum( {$<[Status]={'Active'} >} if(IsNull(CompletedDate),1,0) ).

[ivandrago]

Hi,

Thanks for this, yes, this seems to be returning the correct result but is there another way without using the IF statement as when there is a Large Dataset the calculation will be slower; I did see the functions P() and E() but unsure if this will work,

[Anil_Babu_Samineni]

Either one of the below

=count( {$<[Status]={'Active'}, CompletedDate={"=Len(CompletedDate)=0"} >} [ID] )

=count( {$<[Status]={'Active'}, CompletedDate={"=IsNull(CompletedDate)"} >} [ID] )

=count( {$<[Status]={'Active'}, CompletedDate={"=Null()"} >} [ID] )

=count( {$<[Status]={'Active'}, CompletedDate-={"=Len(CompletedDate)>0"} >} [ID] )

[Vegar · Accepted solution]

Maybe the following expression solves your problem.

=count( {$<
[Status]={'Active'} ,
ID = E({<CompletedDate={"*"}>})
>} [ID] )

[ivandrago]

Hi,

None of these Expressions work? Thy all come back with 0.

[Vegar]

Another solution would be to load null as value during script execution.

In the load script
NullAsValue CompletedDate;
Set NullValue = 'NULL';

LOAD
ID,
CompletedDate,
Status,
...
FROM source;

In the expression:
=count( {$<
[Status]={'Active'} ,
CompletedDate={'NULL'}
>} [ID] )

[nicanova]

Sorry this is years later, but can you explain why this part of the formula works? ID = E({<CompletedDate={"*"}

I would think that using * excludes where the completed date has any value (or isn't null).

I am trying to count how many interviews result in offers extended to candidates, and my formula works (thanks to you!), but I don't understand why it does:

Count({$<[DateType]={'Interview'},[Offer Extended]=E({"[Offer Extended]={"*"}"})>}[Requisition ID])

[BrunPierre]

Hi @nicanova,

An asterisk (*) will return characters (including spaces) except null.

This expression with E() would exclude the Offer Extended field values. It could therefore be interpreted as candidates who did not receive offers.