Filter data based on most recent value from different categories within layer

Question

I have a survey123 survey that different teams have to fill out and submit on a daily basis. I am creating a dashboard to show information on survey submissions within the last 24 hours.

I can filter the data using the date/time of for submission, to be within the last 24 hours, either within the dashboard config itself, or by creating a view layer of the main survery123 feature layer.

One of the elements in my dashboard is a table, and as per the previous paragraph, this currently only has a row/record for teams that have submitted a survey within the last 24 hours. What I would like to display, is a table that has a row/record for every team that exists (whether they have submitted a survey within the last 24 hours or not), and then have some way of visualising the recency of their last submission.

One idea I had was to somehow filter the data on the most recent feature for each team’s name/ID value, so that there would only be 1 record per team, and then style the colour of the row based on whether it was in/out of the last 24 hours. However, I am struggling to identify a way to filter the data based on the most recent record per team.

I also wondered if there would be some way to do this by creating a data expression, but again I am not sure how this would work.

Does anyone have an idea on how I could go about this?

Thanks in advance 🙂

SineKelly17 · Answer

Thanks @Josh this is along the lines of where I was going so helps to confirm that this should work!

I now have a script that technically does work, however it takes a long time for the table to load in the dashboard, due to the significant overhead of functions used in the loop statement to get the top row on the date. The set of functions has to run for every unique teamID, so has a massive overhead.

I have attached an example of the code here, I don't know if you might be able to see a way to optimise this better?

updated_expression_notepad.txt

jcarlson · Answer

A data expression would be the way I'd do it. And I think we could do all the necessary work within a GroupBy statement.

var portal = Portal('your url')

var fs = FeatureSetByPortalItem(
    portal,
    'itemid',
    0, // or whatever layer index it is
    ['teamID', 'date_created'],
    false
)

return GroupBy(fs,
    'teamID',
    {
        name: 'latest_entry',
        expression: 'date_created',
        statistic: 'MAX'
    }
)

Mind that this expression would only be returning the latest date for each team ID. If you wanted to get the data values for those corresponding entries, you'd need to do a bit more work.