topic Re: Find a count of records within a date range using the max date records only in App Development

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

Darren1 — Wed, 17 Aug 2022 17:24:01 GMT

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

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

justISO — Thu, 18 Aug 2022 05:59:38 GMT

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

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

Timario — Thu, 18 Aug 2022 07:28:40 GMT

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

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

Darren1 — Wed, 26 Oct 2022 12:31:42 GMT

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)