Null Condition Set Analysis

[bashar_f]

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

[OmarBenSalem]

Maybe:

Count({

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

+

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

}Distinct [Employee ID])

[bashar_f]

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]

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