10 Replies Latest reply: Aug 18, 2016 12:24 PM by Miguel Braga

Date Range in Set Analysis Expression

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?

• Re: Date Range in Set Analysis Expression

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

• Re: Date Range in Set Analysis Expression

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?

• Re: Date Range in Set Analysis Expression

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

• Re: Date Range in Set Analysis Expression

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.

• Re: Date Range in Set Analysis Expression

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

• Re: Date Range in Set Analysis Expression

Try like this:

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

• Re: Date Range in Set Analysis Expression

Then make this little change:

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

This will make you what you want

Regards,

MB

• Re: Date Range in Set Analysis Expression

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

• Re: Date Range in Set Analysis Expression

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

• Re: Date Range in Set Analysis Expression

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?