Skip to main content
Announcements
Qlik Introduces a New Era of Visualization! READ ALL ABOUT IT
cancel
Showing results for 
Search instead for 
Did you mean: 
Evan0211
Creator
Creator

Null issue between expression and set analysis

Hello,

I have a table that when using this expression it shows 20 rows of data:

=if(Match([OFFICE_CODE], $(vFilterCode))
and [Current Stage] <> 'Pre'
and isnull(B_Completed_Date)
and 
not isnull([P Due Date])
and
not isnull([DD_Completed_Date])

, [Title])

 

...but if I use this is set analysis, the count resolves to 0. The issue is the B_Completed_Date and the isnull() check.

=Count(
{<[OFFICE_CODE]={$(vFilterCode)},
[Current Stage] -={'Pre'},
[B_Completed_Date]= {"=Null()"},
[P Due Date]={"*"},
[DD_Completed_Date] ={"*"},
[Title]={"*"}>
}
[Title])

 

If I don't check on B_Completed_Date, I get he correct number for the amount of rows returned, however I need to return this data ONLY if B_Completed_Date is = null.

Any ideas?

Labels (1)
5 Replies
Or
MVP
MVP

This is a somewhat common question and you can look up multiple past answers, e.g.

https://community.qlik.com/t5/QlikView-App-Dev/Set-Analysis-include-Null-Values-only/td-p/1579255

https://community.qlik.com/t5/QlikView-App-Dev/Set-Analysis-for-Null-and-Not-Null-Values/td-p/922102

If neither of those covers what you need, there's quite a few other threads you can look at for the same question.

Also, generally speaking, do keep in mind that null()=null() evaluates to false, so the approach you tried can't work.

Or_0-1706716565891.png

 

Chanty4u
MVP
MVP

Try this

=Count(

{<[OFFICE_CODE]={$(vFilterCode)},

[Current Stage] -={'Pre'},

[B_Completed_Date] = {"=IsNull()"},

[P Due Date]={"*"},

[DD_Completed_Date] ={"*"},

[Title]={"*"}>

}

[Title])

vinieme12
Champion III
Champion III

Try below

 

=Count(
{<[OFFICE_CODE]={'$(vFilterCode)'},  // quotes for strings, 
[Current Stage] -={'Pre'},
[B_Completed_Date]= {"=len([B_Completed_Date])<1"}, //check for null values
[P Due Date]={"=len([P Due Date])>0"},   //Check for non null values
[DD_Completed_Date] ={"=len([DD_Completed_Date])>0"},  //Check for non null values
>}
[Title])

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.
qv_testing
Specialist II
Specialist II

try this

=Count(
{<[OFFICE_CODE]={$(vFilterCode)}, // String should be in single quote, if not applied in variable
[Current Stage] -={'Pre'},
[B_Completed_Date]= {"=len(TRIM([B_Completed_Date]))=0"}, 
[P Due Date]={"=len(TRIM([P Due Date]))>0"},  
[DD_Completed_Date] ={"=len(TRIM([DD_Completed_Date]))>0"}, 
>}[Title])

Chanty4u
MVP
MVP

Is this solution not working?