Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 
Darren1
Contributor III
Contributor III

Find a count of records within a date range using the max date records only

My goal is to find a count of records within a date range using the max date records only for that date range. For example I would like to find the record count by agency with a max report_date within a date range based on report_date.

I used the following expression to get an accurate KPI count however when I use the same expression in a bar chart the agency counts are not accurate because they are not calculated on the max report_date.

Count({<[report_date] = {"$(='>=' & '07/01/2020' & '<=' & '06/30/2021')"}>} distinct [username])

Below is an example of the data. I would like to count only the highlighted records

Darren1_0-1660756578487.png

 

 

Labels (1)
1 Solution

Accepted Solutions
Darren1
Contributor III
Contributor III
Author

Thank your for help with the expression. 

To make this work I used the expression below in the chart. Then I adjusted the load script to find the most recent username and ordered them by username, report_date. 

Count({<[report_date] = {"$(='>=' & '07/01/2022' & '<=' & '06/30/2023')"}>} distinct username)

 

Darren1_0-1666787309946.png

 

View solution in original post

3 Replies
justISO
Specialist
Specialist

Hi, maybe somebody can give set analysis option for this, but I can provide not so elegant solution to try, which takes max report_date for each username and check if it between date range:

sum(if( aggr(date(max(report_date)), username)>='07/01/2020' and aggr(date(max(report_date)), username)<= '06/30/2021', 1))

Timario
Contributor III
Contributor III

hello. MB will you help next function
aggr( Max( report_date ), username, agency ). You may receive max value of report_date for each username and agency

Darren1
Contributor III
Contributor III
Author

Thank your for help with the expression. 

To make this work I used the expression below in the chart. Then I adjusted the load script to find the most recent username and ordered them by username, report_date. 

Count({<[report_date] = {"$(='>=' & '07/01/2022' & '<=' & '06/30/2023')"}>} distinct username)

 

Darren1_0-1666787309946.png