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?

[#2]

Hi Sunny,

That provides me the count of people who have a Dealer Term Date between 01/01/2015 and 12/31/2015. That expression is not counting the NULLS that have the Dealer Hire Date less than or equal to 12/31/2015.

[#3]

No dimension - just a measure within a KPI.

[sunny_talwar]

Do you have a field such as Dealer Name or Dealer ID which always have a single Dealer Hire Date (or doesn't have anything?)

If you do, you can try this:

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

[#5]

I could be wrong but I feel like this should be close to it?

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

But the underlined section is broken. Thoughts

[sunny_talwar]

Nope that won't work you cannot list same field twice in set analysis. Let's focus on getting this done through the other option. What is one field that uniquely define Dealer Term Date? DealerID? Each Dealer should have one Term Date, is that true?

[#7]

I tweaked it a little -

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

I want to make sure I understand correctly what you're asking. I do have a Dealer Name and or a Dealer Code - but the dates are related to the Students who are employee at the Dealer meaning that each dealer would have Dealer Term Dates for certain employees (students) and a Dealer Hire Date for each employee (student).

With my tweak - it did return a value of 176,918.

The number I'm looking for is 376,228

[#8]

Each Student ID should have either a null or a Dealer Term Date.

[#9]

Meaning that they are either still employed or they have a Dealer Term Date (end of employment with dealership).

[sunny_talwar]

So a Student ID will only ever have a single Dealer Term Date. Is this statement true?

[#11]

True.