Skip to main content
Announcements
Accelerate Your Success: Fuel your data and AI journey with the right services, delivered by our experts. Learn More
cancel
Showing results for 
Search instead for 
Did you mean: 
Rahul88
Contributor II
Contributor II

Count of day between date range

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

2 Solutions

Accepted Solutions
GaryGiles
Specialist
Specialist

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)

View solution in original post

GaryGiles
Specialist
Specialist

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

With completed option:

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

 

View solution in original post

8 Replies
GaryGiles
Specialist
Specialist

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
Contributor II
Contributor II
Author

Thank you GaryGiles for the quick solution. 

The above expressions are giving me a 0. 

 

 

GaryGiles
Specialist
Specialist

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

Rahul88
Contributor II
Contributor II
Author

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
Specialist
Specialist

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

Rahul88
Contributor II
Contributor II
Author

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

Rakesh_0-1614888597050.png

 

Rahul88
Contributor II
Contributor II
Author

All the 3 expressions you mention are returning 0.

GaryGiles
Specialist
Specialist

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

With completed option:

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