Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
ivandrago
Creator II
Creator II

Set Analysis include Null Values only

Hi,

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

Thanks

1 Solution

Accepted Solutions
Vegar
MVP
MVP

Maybe the following expression solves your problem.

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

View solution in original post

8 Replies
eliko_il
Contributor
Contributor

Hi,

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

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

ivandrago
Creator II
Creator II
Author

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

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
Vegar
MVP
MVP

Maybe the following expression solves your problem.

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

Hi,

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

Vegar
MVP
MVP

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
Contributor III
Contributor III

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
Master
Master

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.