Skip to main content
Announcements
See what Drew Clarke has to say about the Qlik Talend Cloud launch! READ THE BLOG
cancel
Showing results for 
Search instead for 
Did you mean: 
bashar_f
Partner - Creator
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
OmarBenSalem

Maybe:

Count({

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

+

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


}Distinct [Employee ID])


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

devyanshu_gupta
Partner - Contributor III
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

OmarBenSalem

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

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