# New to Qlik Sense

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

Announcements
Welcome to Qlik Community! Check out our new navigation! FIND OUT MORE
cancel
Showing results for
Did you mean:  Partner - Creator

## Null Condition Set Analysis

Hi,

In the below expression of a master measure, I want to set the condition to Separation Date is either equal or higher to the variable OR is Null.

Count({<[Seperation Date]= {">=\$(=date(\$(vMinDateInHiresPeriod)))", isNull()}>}Distinct [Employee ID])

The above is basically what I want, but its not working obviously.

The variable part works fine, but adding the null condition is the challenge for me.

Thanks 7 Replies  Partner - Champion II

Maybe:

Count({

<[Seperation Date]= {">=\$(=date(\$(vMinDateInHiresPeriod)))"}>

+

<[Seperation Date]={"=isNull([Seperation Date])=-1"}>

}Distinct [Employee ID])  Partner - Creator
Author

What if the there were more conditions in the set analysis, like so?

Count({<[Date]={"<=\$(=date(\$(vMinDateInHiresPeriod)))"},

[Seperation Date]= {">=\$(=date(\$(vMinDateInHiresPeriod)))"} + [Seperation Date]={"=isNull([Seperation Date])=-1"},

Year =, Month =, Quarter=, [Employee ID]=, [Record Type] = {'Hires','Separation'}>}Distinct [Employee ID])

I tried implementing your answer here, but it didnt work.  Partner - Contributor III

Hi,

When checking null values in set, isnull function works without the brackets().

So try

{<Date={isnull}>} in your set.

I've used this for not equals to it works for that.

Regards,

Devyanshu  Partner - Champion II

"+" is like "or"; so, it would sthing like:

Count({

<

[Date]={"<=\$(=date(\$(vMinDateInHiresPeriod)))"},

[Seperation Date]= {">=\$(=date(\$(vMinDateInHiresPeriod)))"},

Year =, Month =, Quarter=, [Employee ID]=, [Record Type] = {'Hires','Separation'}

>

+

<

[Date]={"<=\$(=date(\$(vMinDateInHiresPeriod)))"},

[Seperation Date]={"=isNull([Seperation Date])=-1"},

Year =, Month =, Quarter=, [Employee ID]=, [Record Type] = {'Hires','Separation'}

>

}Distinct [Employee ID])  MVP

Does you date contains any space or tab space ??  Partner - Champion II

Good remark, since the "definition" of null values could differ...

maybe len(trim(Date))=0 could handle it better..

Just add it then:

Count({

<

[Date]={"<=\$(=date(\$(vMinDateInHiresPeriod)))"},

[Seperation Date]= {">=\$(=date(\$(vMinDateInHiresPeriod)))"},

Year =, Month =, Quarter=, [Employee ID]=, [Record Type] = {'Hires','Separation'}

>

+

<

[Date]={"<=\$(=date(\$(vMinDateInHiresPeriod)))"},

[Seperation Date]={"=isNull([Seperation Date])=-1"},

Year =, Month =, Quarter=, [Employee ID]=, [Record Type] = {'Hires','Separation'}

>

+

<

[Date]={"<=\$(=date(\$(vMinDateInHiresPeriod)))"},

[Seperation Date]={"=len(trim([Seperation Date]))=0"},

Year =, Month =, Quarter=, [Employee ID]=, [Record Type] = {'Hires','Separation'}

>

}Distinct [Employee ID])

and please try this; I think it would also work:

count({

<

[Date]={"<=\$(=date(\$(vMinDateInHiresPeriod)))"},

[Seperation Date]= {">=\$(=date(\$(vMinDateInHiresPeriod)))   OR (=isNull([Seperation Date])=-1) OR (=len(trim([Seperation Date]))=0)"},

Year =, Month =, Quarter=, [Employee ID]=, [Record Type] = {'Hires','Separation'}

>}Distinct [Employee ID])  MVP

Exactly that is the reason it might not get picked and we need to use the len() and Trim() combination to handle this scenario Tags
Community Browser