ArcGIS Arcade DateDiff

[MyriamWright]

Arcade expression in Web Map that can select the most recent date and calculate DateDiff. I have inspection data for the same site with multiple years (ex. 2014, 2015, 2016). I want to calculate (or only show) DateDiff between Date.Now() and - 2016). Thanks!

[XanderBakker]

Hi twoodfield_DawoodGIS , 

I would probably combine the last two examples and do this:

// first read out the ID of the Feature1
var id = $feature.STATE_NAME;
// access the table Feature2
var tbl = FeatureSetByName($map, 'Registration Submissions');
// create a sql expression to query on ID
var sql = "STATE_NAME = '" + ID + "'";
// filter the table using the sql expression
var related_data = Filter(tbl, sql);
// validate if you have any related records
if (Count(related_data)>0) {
    // get the max date and format the result
    var maxepoch = Max(tbl, 'SubmissionDate');
    var maxdate = Date(maxepoch);
    var format = "MM/DD/Y";
    return Text(maxdate, format);
} else {
    // in case you don't have related records
    // define what to return
    return "";
}‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

[XanderBakker]

Hi Timothy Woodfield ,

In addition have a look at the expression below:

// create date 31st of January, note the month is 0:
var dt = Date(2020, 0, 31);
Console("date: " + dt);
// extract the month value (will be 0)
var m = Month(dt);
Console("month: " + m);
// format the date
var format = "MM/DD/Y";
var txt = Text(dt, format);
Console("Formated date: " + txt);

return "OK";‍‍‍‍‍‍‍‍‍‍‍‍

This will write the following to the console:

date: 2020-01-31T00:00:00-05:00
month: 0
Formated date: 01/31/2020‍‍‍

Be aware that when you use a Date function and need to specify the value for the month if will always be a value from 0 to 11. When you extract the month value from a date it will always be a value from 0 to 11. However, when you format a date it will return the month as a value from 1 to 12 as shown in the example.

[Tim-Woodfield]

That worked! Thank you! I used this to set up a months since last submission which looks like this:

// first read out the ID of the Feature1
var id = $feature.STATE_NAME;
// access the table Feature2
var tbl = FeatureSetByName($map, 'Registration Submissions');
// create a sql expression to query on ID
var sql = "STATE_NAME = '" + ID + "'";
// filter the table using the sql expression
var related_data = Filter(tbl, sql);
// validate if you have any related records
if (Count(related_data)>0) {
// get the max date and format the result
var maxepoch = Max(tbl, 'SubmissionDate');
var maxdate = Date(maxepoch);
var endDate = Now();
var diff = DateDiff(endDate,maxdate,'months');
var age = Round(diff,0)
return age
} else {
// in case you don't have related records
// define what to return
return "No Previous Submissions";
}

That all look correct?

Another related question, is there a way to symbolize the polygons by this length since last submission? I thought I saw that the FeatureSetByName function isn't available for the symbology custom expression.

Thanks again,

Tim

[XanderBakker]

Hi Timothy Woodfield ,

Glad to hear that it is working. The code looks OK. I see that you are returning the rounded age in months since the latest submission.

As you noticed in the symbology profile you have no access to related data or other features. Therefore it is not possible to use the latest date to symbolize your features. You can calculate a field to obtain the information, but this will not automatically update when the data changes. In Enterprise and Desktop you could use an Attribute Calculation Rule to update the age of the latest submission, but this functionality is not yet available in ArcGIS Online. As an alternative you could schedule a task in Pro with a certain frequency to update the field, but changes made to the data will not be reflected in the symbology.

[Tim-Woodfield]

Hey Xander,

Another followup question for you regarding the related records. How would I access another field from that row in the related record? I would still want to reference the row with the max date but would want to grab a name field associated with that record instead.

Thanks!

Tim

[XanderBakker]

Hi Timothy Woodfield , 

Since you are using a Max function, you won't be able to get the maximum value and the related name of that record. This would require you to loop through the records and every time your date is higher than the previous maximum date, you would update the name of the maximum date found in previous records. 

[MyriamWright]

Thanks you for replying!

So, I used the following script modified with my field name from the developer.arcgis.com. It does calculate the DateDiff, but if I have more than one dates over the years, it calculates it for every single year. I am only looking for the last time an inspection was done. I am using ArcGIS online with data from a feature service. I am really new to Arcade and programming. 

var startDate = $feature.StartDate;
var endDate = Now()
var retAge = null;

if (startDate != null && endDate != null) {
startDate = Date(startDate);
endDate = Date(endDate);
retAge = DateDiff(endDate, startDate, "years");
}
return retAge;

[MyriamWright]

 So, it looks like the FeatureSetByName is not available in my ArcGIS online.

[XanderBakker]

If you are using ArcGIS Online, FeatureSetByName is available. However, it is not available for every for every "profile". You can use these functions in pop-up window and for fields calculations, but it will not be available for the symbology. However, you can perform a field calculation and symbolize on the new field (this will not yield a dynamic result).

Maybe you can share some screenshot and explain a little more what you are trying to achieve? 

[KenBuja]

Are you using Portal? If so, that's usually one or more versions behind the capabilities in ArcOnline.

About release versions—Portal for ArcGIS | ArcGIS Enterprise