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

[Darren1]

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

[justISO]

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]

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 · Accepted solution]

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)