Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join this live chat April 6, 10AM EST - QlikView to Qlik Sense REGISTER
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
Partner
Partner

Maybe the following expression solves your problem.

=count( {$<
[Status]={'Active'} ,
ID = E({<CompletedDate={"*"}>})
>} [ID] )
Plees ekskuse my Swenglish and or Norweglish spelling misstakes

View solution in original post

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

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

Maybe the following expression solves your problem.

=count( {$<
[Status]={'Active'} ,
ID = E({<CompletedDate={"*"}>})
>} [ID] )
Plees ekskuse my Swenglish and or Norweglish spelling misstakes

View solution in original post

ivandrago
Creator II
Creator II
Author

Hi,

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

Vegar
Partner
Partner

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] )
Plees ekskuse my Swenglish and or Norweglish spelling misstakes