Count of day between date range

[Rahul88]

Hello Experts,

I am trying to make 3 separate KPI's in which I want to see the count of data for

1. Last 90 days
2. 179 day -91 day
3. 181 days and greater

I have a date column MM/DD/YYYY and an ID column 

Thank you

[GaryGiles · Accepted solution]

Replace [date column] with the name of your actual date column and try this:

1.  count({$<[date column]={">$(=Date(Today()-90))"}>} ID)

2.  count({$<[date column]={">=$(=Date(Today()-179))<=$(=Date(Today()-91))"}>} ID)

3.  count({$<[date column]={"<=$(=Date(Today()-180))"}>} ID)

[Rahul88]

Thank you GaryGiles for the quick solution. 

The above expressions are giving me a 0. 

[GaryGiles]

Can you the expressions that you used?  Is your date field a true date?

[Rahul88]

So I am getting the date in "2015-10-18 00:00:00" from the database which I am converting to

" Date(Floor("DATE"), 'MM-DD-YYYY')    AS "DATE" in the load editor. 

[GaryGiles]

I meant post the expression(s) that are returning 0.

[Rahul88]

Also, if I use the below expression is giving me the correct count. 

Last 90 days:    Count({$<[DATE] = {"=[DATE] > (Today()-90)"}>} [ID])

181 day and greater:  Count({$<[DATE] = {"=[DATE] < (Today()-180)"}>} [ID])

I am not sure how to write it for 179 day -91 days.

Also, how can I add a where clause to the above expression.

Eg.  count of data for the last 90 day where complete is true

[Rahul88]

All the 3 expressions you mention are returning 0.

[GaryGiles · Accepted solution]

count({$<[DATE]={">=(Today()-179)<=(Today()-91)"}>} ID)

With completed option:

count({$<[DATE]={">=(Today()-179)<=(Today()-91)"},[completed]={'TRUE'}>} ID)