Qlik Community

Ask a Question

New to Qlik Sense

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

cancel
Showing results for 
Search instead for 
Did you mean: 
bashar_f
Partner
Partner

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
Partner
Partner

Maybe:

Count({

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

+

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


}Distinct [Employee ID])


bashar_f
Partner
Partner
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.

devyanshu_gupta
Partner
Partner

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
Partner
Partner

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

avinashelite

Does you date contains any space or tab space ??

OmarBenSalem
Partner
Partner

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

avinashelite

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