Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

bashar_f
New Contributor II

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
OmarBenSalem
Esteemed Contributor

Re: Null Condition Set Analysis

Maybe:

Count({

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

+

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


}Distinct [Employee ID])


bashar_f
New Contributor II

Re: Null Condition Set Analysis

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.

devyanshu_gupta
New Contributor III

Re: Null Condition Set Analysis

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

OmarBenSalem
Esteemed Contributor

Re: Null Condition Set Analysis

"+" 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])

Re: Null Condition Set Analysis

Does you date contains any space or tab space ??

OmarBenSalem
Esteemed Contributor

Re: Null Condition Set Analysis

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

Re: Null Condition Set Analysis

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

Community Browser