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]

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

[Tim-Woodfield]

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

[XanderBakker]

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.

[Tim-Woodfield]

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":

// 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');
// return the result
return result;

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

[XanderBakker]

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.

[Tim-Woodfield]

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

[XanderBakker]

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‍

[XanderBakker]

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.

[Tim-Woodfield]

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?