Arcade expression for change since last edit?

686
10
Jump to solution
05-26-2020 10:59 AM
ScottStopyak
Occasional Contributor II

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:

CountyNewCasesTodayDate
Eaton1205/26/2020
Eaton1005/27/2020
Eaton1405/29/2020
Eaton1205/30/2020
Eaton306/3/2020

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

0 Kudos
1 Solution

Accepted Solutions
XanderBakker
Esri Esteemed Contributor

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.

View solution in original post

0 Kudos
10 Replies
XanderBakker
Esri Esteemed Contributor

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.

0 Kudos
ScottStopyak
Occasional Contributor II

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!

ScottStopyak
Occasional Contributor II

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

0 Kudos
XanderBakker
Esri Esteemed Contributor

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?

0 Kudos
ScottStopyak
Occasional Contributor II

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?

0 Kudos
XanderBakker
Esri Esteemed Contributor

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.

0 Kudos
ScottStopyak
Occasional Contributor II

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?

0 Kudos
XanderBakker
Esri Esteemed Contributor

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.

0 Kudos
XanderBakker
Esri Esteemed Contributor

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;
0 Kudos