topic Re: ArcGIS Arcade DateDiff in ArcGIS Online Questions

ArcGIS Arcade DateDiff

MyriamWright — Thu, 20 Dec 2018 22:36:34 GMT

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!

Re: ArcGIS Arcade DateDiff

XanderBakker — Fri, 21 Dec 2018 18:56:23 GMT

I'm not sure if I understand this correctly, but you have inspection locations multiple inspections (/2014, 2015 and 2016) for those locations. You want to calculate the time that has elapsed for each location inspection since the last inspection for that location?

If this is the case, this has become possible with the last update of AGOL on December, 5th.If you want to show this elapsed time since in the pop-up of the inspection locations, you can do something like this:

Access the table with the inspection history using FeatureSetByName (using $map or $datastore to access the table)
Filter the inspection history table using the ID of the current inspection location (I assume your table has this information)
Use Max(tbl, DateFieldName) to get the maximum inspection date for that location
Use DateDiff to calculate the elapsed time since the last inspection

Re: ArcGIS Arcade DateDiff

MyriamWright — Fri, 21 Dec 2018 19:58:35 GMT

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;

Re: ArcGIS Arcade DateDiff

MyriamWright — Fri, 21 Dec 2018 20:16:15 GMT

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

Re: ArcGIS Arcade DateDiff

XanderBakker — Sun, 23 Dec 2018 16:24:41 GMT

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?

Re: ArcGIS Arcade DateDiff

KenBuja — Wed, 26 Dec 2018 18:48:19 GMT

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

Re: ArcGIS Arcade DateDiff

MyriamWright — Wed, 26 Dec 2018 19:30:04 GMT

Not portal. I will check wit IT. Thanks!

Re: ArcGIS Arcade DateDiff

Tim-Woodfield — Tue, 08 Sep 2020 18:29:28 GMT

Hi Xander,

Would this expression work with a related table? We would be trying to track time since last submission. We have a feature service with a polygon of every state and a related table with all submissions an their dates. Could we use this FeatureSetByName function to call the related table, filter for the max date to find the most recent one for a particular state, and then use the DateDiff to compare against Now()?

Thanks!

Tim

Re: ArcGIS Arcade DateDiff

XanderBakker — Tue, 08 Sep 2020 19:48:25 GMT

Hi Timothy Woodfield ,

In the pop-up you can certainly do this. We can have a look at this in more detail, but I would recommend to start a new question to avoid creating noise in this one.

Re: ArcGIS Arcade DateDiff

Tim-Woodfield — Tue, 08 Sep 2020 20:12:12 GMT

Xander Bakker‌

Hi again Xander,

I had another question come up relating to this while working through the previous one. I was able to grab the expression from one of your other posts but am having a hard time getting the date in the format I need. I currently have this working but the date is being returned as "1599177600000":

I tried to reformat using the date functions but am getting "NaN/Nan/Nan" as the string using 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);
// initiate a variable to hold the result
var result = Max(tbl, 'SubmissionDate');
var relateddate = Month(result)+"/"+Day(result)+"/"+Year(result)
// return the result
return relateddate;

Is there another way I can get the date in a MM/DD/YYYY format from 'result'?

Thanks!

Tim

Re: ArcGIS Arcade DateDiff

XanderBakker — Sun, 12 Dec 2021 09:00:24 GMT

Hi Timothy Woodfield ,

See the example below:

// 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 maxdate = Max(tbl, 'SubmissionDate');
    var format = "MM/DD/Y";
    return Text(maxdate, format);
} else {
    // in case you don't have related records
    // define what to return
    return "";
}
‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

On lines 13 and 14 the date is formatted using a defined format.

I would also recommend you to use the FeatureSetByRelationshipName function in case the related records are related through a RelationshipClass.

Re: ArcGIS Arcade DateDiff

Tim-Woodfield — Tue, 08 Sep 2020 21:49:42 GMT

Hey Xander,

Thanks for getting back so quick. I tried that out and got an error that says, "Execution Error:unable to find a number expression in pattern: MM/DD/Y".

I was able to set up another one that worked but it seems to be returning the wrong date. The related record has a date of 9/8/2020 but the expression is returning 8/8/2020. I tried it with a few others and it seemed to always be off by a month. This was the code:

// 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);
// initiate a variable to hold the result
var result = Max(tbl, 'SubmissionDate');
var relateddate = Date(result)
var formatteddate= Month(relateddate)+"/"+Day(relateddate)+"/"+Year(relateddate)
// return the result
return formatteddate;

I'm guessing it's something to do with one of the Date functions. If I have it return 'result', it gives me the number 1599609600000. I checked this on epochconverter.com and it came back as 9/8/2020 at 9:40:47 PM GMT. I tried returning relateddate and that came back as 08 Sep 2020 08:00:00 pm so the date is correct at that point but gets skewed by the Month() function.

Any idea of what might be throwing it off by a month? Is there another way instead of the Month, Day, and Year function to format the date?

Thanks!

Tim

Re: ArcGIS Arcade DateDiff

XanderBakker — Sun, 12 Dec 2021 09:00:26 GMT

Hi twoodfield_DawoodGIS ,

I guess you don't have a date but an epoch in the field. Are you sure, the field is not a date but numeric?

You could do something like this (provide the epoch to the Date function to get a date and then format it):

var maxepoch = 1599609600000;
var maxdate = Date(maxepoch);
var format = "MM/DD/Y";
return Text(maxdate, format);‍‍‍‍

This returned:

09/08/2020‍

Re: ArcGIS Arcade DateDiff

XanderBakker — Tue, 08 Sep 2020 22:01:46 GMT

Hi Timothy Woodfield ,

About the other question. Your month is 1 month off, since the Month function and Date function in Arcade (and JavaScript) expect you to specify the month as a value from 0 to 11 and not 1 to 12.

Re: ArcGIS Arcade DateDiff

Tim-Woodfield — Tue, 08 Sep 2020 22:14:28 GMT

What would be the best way to reformat the months to go from 0-11 and not 1-12? Is that something I need to set in ArcMap or Pro before publishing the feature service?

Re: ArcGIS Arcade DateDiff

XanderBakker — Sun, 12 Dec 2021 09:00:29 GMT

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 "";
}‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

Re: ArcGIS Arcade DateDiff

XanderBakker — Sun, 12 Dec 2021 09:00:32 GMT

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.

Re: ArcGIS Arcade DateDiff

Tim-Woodfield — Tue, 08 Sep 2020 23:25:27 GMT

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

Re: ArcGIS Arcade DateDiff

XanderBakker — Wed, 09 Sep 2020 14:22:25 GMT

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.

Re: ArcGIS Arcade DateDiff

Tim-Woodfield — Thu, 10 Sep 2020 20:53:20 GMT

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