Set Analysis Expression with date range & null

[#1]

Hello,

What's wrong with my expression?

Count({<[Dealer Hire Date] = {'<=12/31/2015'},[Dealer Term Date] = {"$(= '>=' & '01/01/2015' & '<=' & '12/31/2015')" or null}>}[Student ID])

I am trying to count Student ID where their Dealer Hire Date is Less Than or Equal to 12/31/2015 AND they either have a Dealer Term Date in-between the dates of 01/01/2015 - 12/31/2015 OR they don't have a Dealer Term Date.

My KPI object keeps returning the value of 0. I have a feeling my problem fall within how I am writing in the expression 'OR NULL'.

Suggestions on how to write this?

[sunny_talwar]

Try this, I gave you incorrect expression:

Count({<[Dealer Hire Date] = {'<=12/31/2015'},[Dealer Term Date] = {"$(= '>=' & '01/01/2015' & '<=' & '12/31/2015')"}>+<[Dealer Hire Date] = {'<=12/31/2015'}, [Student ID] = {"=Len(Trim([Dealer Term Date])) = 0"}>}[Student ID])

[#3]

This gave me 544,928.

Let me try to explain further.

I want the Beginning Population for the year 2015.

Beginning Population for 2015 = Anyone who was hired [Dealer Hire Date] before and up until 12/31/2015. But also who is still employed (meaning they don't have a [Dealer Term Date] or they have a [Dealer Term Date] between the dates of 01/01/2015 - 12/31/2015.

[sunny_talwar]

That's what it should have been doing, try this one time:

Count({<[Dealer Hire Date] = {"$(='<=' & '12/31/2015')"},[Dealer Term Date] = {"$(= '>=' & '01/01/2015' & '<=' & '12/31/2015')"}>+<[Dealer Hire Date] = {"$(='<=' & '12/31/2015')"}, [Student ID] = {"=Len(Trim([Dealer Term Date])) = 0"}>}[Student ID])

[#5]

Example Data =

We want to count every single one of these rows because all statements are true.

They have a Dealer Hire Date before 12/31/2015.

They have a Null Dealer Term Date or a Dealer Term Date between the dates of 01/01/2015 and 12/31/2015.

Dealer Hire Date	Dealer Term Date
10/1/2014
10/2/2014
10/28/2003
2/7/2014
2/7/2014
3/26/2014
9/6/2003
9/12/2003
3/10/2004
2/7/2014
9/17/2013
2/9/2005
2/11/2005
2/7/2014
3/26/2014
5/3/2004
2/7/2014
9/17/2013
2/9/2005
2/11/2005
2/7/2014
3/26/2014
11/24/2011
11/28/2011
2/7/2014
2/7/2014
3/26/2014
9/6/2003
9/12/2003
3/10/2004
2/12/2009
2/7/2014
9/17/2013
1/30/2015
2/9/2005
2/11/2005
2/7/2014
3/26/2014
9/6/2003
9/12/2003
3/10/2004
2/7/2014
9/17/2013
2/9/2005
2/11/2005
2/7/2014
3/26/2014
10/23/2003
2/7/2014
2/7/2014
3/26/2014
9/12/2003
3/10/2004
2/12/2009
4/23/2009
2/7/2014
1/30/2015
2/9/2005
2/11/2005
2/7/2014
3/26/2014
10/28/2003
2/7/2014
2/7/2014
3/26/2014
10/28/2003
2/7/2014
2/7/2014
3/26/2014
10/7/2015
9/6/2003
2/7/2014
2/9/2005
2/11/2005
2/7/2014
3/26/2014
9/6/2003
9/12/2003
3/10/2004
2/12/2009
2/7/2014
1/30/2015
2/9/2005
2/11/2005
2/7/2014
3/26/2014
9/6/2003
9/12/2003
3/10/2004
2/12/2009
2/7/2014
9/17/2013
1/30/2015
2/9/2005
2/11/2005
2/7/2014
3/26/2014
10/28/2003
2/7/2014
2/7/2014
3/26/2014
1/26/2012
10/23/2003
2/7/2014
2/7/2014
3/26/2014
10/23/2003
2/7/2014
2/7/2014
3/26/2014
9/6/2003
9/12/2003
3/10/2004
2/7/2014
9/17/2013
2/9/2005
2/11/2005
2/7/2014
3/26/2014
1/8/2014	1/1/2015
10/24/2015
10/26/2015
10/28/2003

[#6]

This still returns the value of 544,928

[sunny_talwar]

How about adding a Distinct in there? Would that make a difference?

Count(DISTINCT {<[Dealer Hire Date] = {"$(='<=' & '12/31/2015')"},[Dealer Term Date] = {"$(= '>=' & '01/01/2015' & '<=' & '12/31/2015')"}>+<[Dealer Hire Date] = {"$(='<=' & '12/31/2015')"}, [Student ID] = {"=Len(Trim([Dealer Term Date])) = 0"}>}[Student ID])

[sunny_talwar]

I will try to create a sample with this data in a little bit

[#9]

That gives me 124,024. I don't think we want distinct though. We want to track how often the student jumps position to position within the dealership so they need to be accounted for each time they have a Hire or Term Date.

[sunny_talwar]

Hmmm that make sense, but if that is true, it seems that a particular student ID can have two Term Dates? One with one dealer and another one for another dealer? Is that true?

Count({<[Dealer Hire Date] = {"$(='<=' & '12/31/2015')"},[Dealer Term Date] = {"$(= '>=' & '01/01/2015' & '<=' & '12/31/2015')"}>+<[Dealer Hire Date] = {"$(='<=' & '12/31/2015')"}, [Student ID] = {"=Len(Trim(Concat([Dealer Term Date]))) = 0"}>}[Student ID])

Sorry for flooding your post, but I am trying my best

[#11]

Here is where I am getting 376,228 from.

Hope it helps! I appreciate it!