Skip to main content

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
NEW webinar Dec. 7th: 2023 Outlook, A Pivotal Year for Data Integration SIGN ME UP!
cancel
Showing results for 
Search instead for 
Did you mean: 
MPS
Contributor III
Contributor III

Set Analysis 'Includes blank'

Need help with the below expression. I want to include the blanks from the field name 'Active End Date'. This field has Dates and rest as blanks. 

the below expression does select the date with dates, if i remove 'not equals'. 

Sum({<Active__c={'1'},[Property Record Type]={'Office'}
,Active_End_Date-={"*"}>} distinct [Usable Area])

 

Labels (1)
14 Replies
MPS
Contributor III
Contributor III
Author

So sorry, the expression did go okay without an error, but the data remains showing the both values (null & dates)

Chanty4u
MVP
MVP

one more try

Add these two lines near the before your data load in the script:

NULLASVALUE;

SET NullValue = 'Blank';  (or SET NullValue = ' ' if applicable)

 

Sum({<Active__c={'1'},[Property Record Type]={'Office'}
,Active_End_Date={"=len(trim(Active_End_Date))=0"}>} distinct [Usable Area])

 

agigliotti
Partner - Master III
Partner - Master III

sorry, my mistake...

=Sum( distinct {< Active__c = {'1'}, [Property Record Type] = {'Office'} >} if( IsNull(Active_End_Date), [Usable Area] ) )

or

=Sum( distinct {< Active__c = {'1'}, [Property Record Type] = {'Office'} >
*
1-< Active_End_Date = {"*"} >} [Usable Area] )

MPS
Contributor III
Contributor III
Author

Thank you all, managed to get the answer. sorry for the late reply.

Sum({<Active__c={'1'},[Property Record Type]={'Office'}
,[Property Location ID]={"=len(Active_End_Date)=0"}>} distinct [Usable Area])

 

jlongoria
Creator
Creator

Based on what you are trying to do, I recommend creating a flag/identifier at load time to identify null and/or empty values then using the identifier in the set analysis. For example, if you create a flag called IsActiveDateNull where 1 is true and 0 is false, you can use IsActiveDateNull={"1"} in your set analysis.