7 Replies Latest reply: Jun 12, 2018 6:41 AM by Avinash R

# 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

• ###### Re: Null Condition Set Analysis

Maybe:

Count({

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

+

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

}Distinct [Employee ID])

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

• ###### 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

Hi,

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

So try

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

Regards,

Devyanshu

• ###### Re: Null Condition Set Analysis

Does you date contains any space or tab space ??

• ###### Re: Null Condition Set Analysis

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

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

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