Date Range in Set Analysis Expression

[#1]

I need help.

Problem -

I have a date field -

[Dealer Term Date]

My Expression =

count({<[Dealer Term Date] = {">={03/31/2016}"}>}[Student ID])

I am trying to get a count of [Student ID] where the [Dealer Term Date] is less than 03/31/2016.

I'm not getting any value. What am I doing wrong?

[Anonymous]

Hi

try this if you looking for less than 03/31/2016

count({<[Dealer Term Date] = {"<=03/31/2016"}>}[Student ID])

or

Let vEndDate=Floor(MakeDate(2016,03,31));

count({<[Dealer Term Date] = {"<=$(=Date(vEndDate,'MM/DD/YYYY'))"}>}[Student ID])

[miguelbraga]

Hey there,

Simply put the minus simbol bifore curve brackets like this:

count({<[Dealer Term Date] = - {">={03/31/2016}"}>}[Student ID])

Hope this helps you

Regards,

MB

[#4]

Hello,

I appreciate your help on this. Could we go one step further -

I need a count of [Student ID] where their [Dealer Hire Date] is between 01/01/2016 and 03/31/2016 and the [Dealer Term Date] is also between 01/01/2016 and 03/31/2016.

So it would be that they were hired and fired during this period of time (Quarter 1 of the year).

I can't seem to get my mind to create this. Thoughts?

[#5]

Hello,

I appreciate your help on this. Could we go one step further -

I need a count of [Student ID] where their [Dealer Hire Date] is between 01/01/2016 and 03/31/2016 and the [Dealer Term Date] is also between 01/01/2016 and 03/31/2016.

So it would be that they were hired and fired during this period of time (Quarter 1 of the year).

I can't seem to get my mind to create this. Thoughts?

[miguelbraga]

Maybe this?

count({<[Dealer Hire Date] = {"$(= '>=' & '01/01/2016' & '<=' & '03/31/2016')"}>} [Student ID])

+

count({<[Dealer Term Date] = {"$(= '>=' & '01/01/2016' & '<=' & '03/31/2016')"}>} [Student ID])

or eve thi?

vStartDate: date#('01/01/2016','MM/DD/YYYY')

vEndDate: date#('03/31/2016','MM/DD/YYYY')

count({<[Dealer Hire Date] ={'>=$(vStartDate)<=$(vEndDate)'}>} [Student ID])

+

count({<[Dealer Term Date] ={'>=$(vStartDate)<=$(vEndDate)'}>} [Student ID])

Good luck

[#7]

That didn't work. I don't want to add them together, I only want to count them if that have a start date and end date in that date range (01/01/2016 - 03/31/2016).

count({<[Dealer Hire Date] = {"$(= '>=' & '01/01/2016' & '<=' & '03/31/2016')"}, {"$(= '>=' & '01/01/2016' & '<=' & '03/31/2016')"}>} >} [Student ID])

^ I think it would look something like this? It would be another selection within the set analysis.

[sunny_talwar · Accepted solution]

How about this:

Count({<[Dealer Term Date] = {'>=01/01/2016<=03/31/2016'}, [Dealer Hire Date] = {'>=01/01/2016<=03/31/2016'}>}[Student ID])

[#9]

Oh hey, I think what I just wrote above worked. Haha!

[sunny_talwar]

Try like this:

Count({<[Dealer Hire Date] = {"$(= '>=' & '01/01/2016' & '<=' & '03/31/2016')"}, [Dealer Hire Date] = {"$(= '>=' & '01/01/2016' & '<=' & '03/31/2016')"}>} [Student ID])