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]

Please flood away!!!! Haha! I need help!

This gave me 233,968

[sunny_talwar]

Alright lets break this down into two expressions and see what we get:

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

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

See which of the two expressions are incorrect here

[#4]

Hmmm. Well technically, I guess the Student ID could have multiple Dealer Term Dates with the same or different Dealer. They would each get their own row for each start and end date.

Are you saying I need to be able to bring in another qualifier? I have Position Codes but it's possible they quit as a sales man but came back as a sales man.

[sunny_talwar]

So I guess are we counting a Student ID which left and joined another guy or are we not counting them? In the above case if a student id never had a Dealer Term Date is only time he will be counted in using the Concat() thing.

Student ID     Dealer Term Date

1,    

1,      10/20/2016

2,

Here 1 won't be counted, 2 will be. Do we want both 1 and 2 to be counted or just 1 (2nd Student ID

[#6]

1) This gives me the total count of Student Ids that have a Dealer Term Date in 2015  (49,080)

2) Gives me the value of 184,888   <- Not sure what this is a real count of.

[#7]

Student ID    Dealer Term Date

1,                             < We would count them (BUT ONLY IF THEIR HIRE DATE IS BEFORE 12/31/2015

1,      10/20/2016     < would not be counted because the Term Date is in 2016

2,                             < We would count them (BUT ONLY IF THEIR HIRE DATE IS BEFORE 12/31/2015

[sunny_talwar]

But the first two rows are the same Student ID, you will calculate him once? Is that true?

[#9]

Here is a smaller of sample of data to work with.

[#10]

In regards to the specifics that we are looking at he would get counted - 1 time. He wouldn't get counted a second time because the 3rd statement (having a Dealer Term Date before 12/31/2015) is false.

[#11]

We would count every line because the statements are true -

1) they have a hire date before 12/31/2015

2) they have a Dealer Term Date between 1/1/2015 and 12/31/2015

or

3) The Dealer Term Date is = NULL because they haven't ended their employment