topic Re: Arcade expression for change since last edit? in ArcGIS Online Questions

Arcade expression for change since last edit?

ScottStopyak — Tue, 26 May 2020 17:59:58 GMT

I have a COVID-19 dashboard that needs to show the change in the number of cases since the last report. I have a table with a date field but the reporting isn't daily, so I can't just filter by things like "Today" or "Yesterday" etc. I need to find the max date and second to max date from the report records and report the change and/or rate of change.

The data looks something like this:

County	NewCasesToday	Date
Eaton	12	05/26/2020
Eaton	10	05/27/2020
Eaton	14	05/29/2020
Eaton	12	05/30/2020
Eaton	3	06/3/2020

I've used Arcade for simpler calculations, but this one has me stuck. Any help would be most appreciated.

Re: Arcade expression for change since last edit?

XanderBakker — Tue, 26 May 2020 19:58:48 GMT

Hi Scott Stopyak ,

You could use Arcade to calculate the difference, but this would probably requiere a field calculation to use this in an indicator (if that where you want to show it). When I browse through the help for indicators (Indicator—ArcGIS Dashboards | Documentation ), it states this:

Reference values on indicators are optional and, when specified, can be thought of as a predefined goal or threshold. There are three types of reference values: the indicator's previous value, a fixed value set at design time, or a statistic calculated at run time.

Not sure if this works for your situation.

Re: Arcade expression for change since last edit?

ScottStopyak — Tue, 26 May 2020 20:23:37 GMT

Thanks, Xander. I think I can tease out the most recent report using max(date) but then I need to compare it to the second most recent report. Since I can't just say "yesterday" (reports don't happen every day), I'm stuck there. I imagine it must be possible to sort the dates somehow and choose the second most recent to use as a reference to compare to the most recent. I just can't seem to figure out how that would be accomplished in arcade. Maybe I have to do this calc on the backend with Python? I was hoping to avoid schema changes though, if I can.

Update: Now I see what you mean with the indicator. It has built in field {} calcs that refer to the previous value. That in conjunction with conditionally formatting for increases and decreases will work fine. Thank you!

Re: Arcade expression for change since last edit?

ScottStopyak — Thu, 28 May 2020 13:30:41 GMT

The previous value setting didn't work for me after all. The data changed but the percent change remained zero.

Re: Arcade expression for change since last edit?

XanderBakker — Thu, 28 May 2020 16:56:31 GMT

Hi Scott Stopyak ,

I'm sorry to hear that. Would using a field calculation or defining a scheduled task in Pro be an option you would consider?

Re: Arcade expression for change since last edit?

ScottStopyak — Thu, 28 May 2020 17:45:29 GMT

I figured out there's a bug in the previous value reference within the indicator widget and fixing it is "Not in current product plan". The data that would be easiest to use is hosted on AGO so a scheduled task seems unlikely without getting into a lot of API stuff. Another dataset is hosted from an internal server that could maybe be query layered into the map service as a table. I was looking for more of a "set it and forget it" solution like the indicator would be if they'd fix it. Out of curiosity though, what kind of field calc would be needed? Would it be a one time thing or would it have to be recalculated every day?

Re: Arcade expression for change since last edit?

XanderBakker — Thu, 28 May 2020 18:42:24 GMT

Hi Scott Stopyak ,

A field calculation would not be a huge things and processing time depends largely on the volume of data that needs to be processed. The frequency of performing the field calculation depends on what best suits your needs. There is not much API involved in this since ArcGIS Pro can directly access the data on AGOL and update it and in Pro you can schedule the field calculation.

Re: Arcade expression for change since last edit?

ScottStopyak — Thu, 28 May 2020 18:58:36 GMT

Thanks. That could work if I can conjure an expression that'll calc the difference between the most recent and second most recent rows. Maybe create sorted lists in python and grab the first two values and subtract them? Does that sound about right?

Re: Arcade expression for change since last edit?

XanderBakker — Thu, 28 May 2020 23:13:09 GMT

Hi Scott Stopyak ,

When you want to use a field calculation that does the job, for this specific case it will be much simpler to use Arcade. You can also create a standalone Python script to do the job, but this might requiere a little more lines of code.It really depends on where the data is stored. If the table and the feature layer are in the same datastore I recommend using Arcade. Is that the case? If there is a way to have access to the data I can have a look at what the expression should look like. You can share the data in a group and invite me to it "xbakker.spx". Arcade has some pretty helpful functions to access related data or filter it and using OrderBy enables you to sort the data by the data and Top will allow you to get the top 2 items and extract the information you are looking for.

Re: Arcade expression for change since last edit?

XanderBakker — Sun, 12 Dec 2021 06:27:32 GMT

Hi EatonCountySS ,

Just to share a little insight in what the expression may look like:

// get related records
var fs = FeatureSetByRelationshipName($feature, "Name of the relationship to get to related records");

// sort the related records and get top 2
fs = Top(OrderBy(fs, 'DATE DES'), 2);

// get the latest and previous features
var result = Null;
if (Count(fs)==2) {
    var cnt = 0;
    for (var f in fs) {
        cnt += 1;
        if (cnt == 1) {
            var f_latest = f; 
        } else if (cnt == 2) {
            var f_prev = f;
        }
    }

// determine NewCasesToday and calculate increment
var cases_latest = f_latest.NewCasesToday;
var cases_prev = f_prev.NewCasesToday;
result = cases_latest - cases_prev;
}

// return the result
return result;‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

Re: Arcade expression for change since last edit?

ScottStopyak — Fri, 29 May 2020 16:39:04 GMT

Thanks for that. Much appreciated. I'm not all that familiar with arcade yet so I'm thinking the best approach for me is to add a previous count field to the hosted feature class. Next, I'll start doing my edits in Pro rather than on AGO directly so all edits are sent simultaneously when I save them. Before I do my daily edits to update the current counts, I'll just do a field calc on the previous count field, pushing the current values over. Then I update the current case counts. The indicator should then (in theory) work because I can provide an attribute value as a reference. Takes a few more seconds to do it that way, but it should be foolproof, I hope.

Re: Arcade expression for change since last edit?

AJM — Thu, 28 Sep 2023 13:09:32 GMT

@XanderBakker

I am trying to do something similar and calculate the the change in lake level from the most recent reading to the previous reading. I tried the code and just changed the feature and date fields to start with and get and error at the ; at the end.

Test execution error: Unexpected token '${value}'.. Verify test data.

// get related records var fs = FeatureSetByRelationshipName($feature, 'lake_level'); // sort the related records and get top 2 fs = Top(OrderBy(fs, '_date DES'), 2); // get the latest and previous features var result = Null; if (Count(fs)==2) { var cnt = 0; for (var f in fs) { cnt += 1; if (cnt == 1) { var f_latest = f; } else if (cnt == 2) { var f_prev = f; } } // determine NewCasesToday and calculate increment var cases_latest = f_latest.NewCasesToday; var cases_prev = f_prev.NewCasesToday; result = cases_latest - cases_prev; } // return the result return result;

Any help would be appreciated. Thanks