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: 
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)
1 Solution

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

 

View solution in original post

14 Replies
agigliotti
Partner - Champion
Partner - Champion

Hi @MPS ,

The statement   Active_End_Date -= {"*"}  will exclude all field values.

Instead    Active_End_Date = {"*"}  will exclude only null values in the field.

I hope it can help.

Best Regards

Chanty4u
MVP
MVP

try this

How about checking isnull() and update it to 0 

like If(Field=Null(),0,Fieldas Field1

 

or

script

if(len(Trim(Field)=0,0,Field) AS Field1

MPS
Contributor III
Contributor III
Author

Hi @agigliotti 

Thanks for your response. 

I actually want to include the Null values. I used 'Active_End_Date = {"*"}' which included the dates  record, and excluded null values. 

Infact i need the opposite, to exclude the values and read only null values. 

Hope i didnt confuse you. 

 

 

 

 

Chanty4u
MVP
MVP

can you try this

Sum({1-< _Field1= {'*'} >} UsableArea )

agigliotti
Partner - Champion
Partner - Champion

let's try with this:

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

Saravanan_Desingh

Try like this,

Sum({<Active__c={'1'},[Property Record Type]={'Office'}
,[Usable Area]={"=IsNull(Active_End_Date)"}>} distinct [Usable Area])
MPS
Contributor III
Contributor III
Author

Hi @Chanty4u ,

Thank you for your response.

can you please help me to write the full expression to the below by adding your script shared

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

Chanty4u
MVP
MVP

first try this if it is working 

Sum({1-<,Active_End_Date={"*"}>} distinct [Usable Area])

 

then add this

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

and try to keep this in script

NullAsValue  Active_End_Date;

Set NullValue = 'NULL'; 

 

MPS
Contributor III
Contributor III
Author

Got Error in expression