Calculating Difference Between Two Dates using Arcade Expressions

[BNix_TPA]

I'm trying to add a new variable to my data set that is the difference between two dates, End and Start Date.  I plan on using it in an indicator to show average time to close a WO.  I created a new Arcade expression, but am receiving an error with it. Maybe there is a between way to achieve this than using an Arcade Expression.  I was able to add this data to the pop-ups, but am having trouble recreating within the dashboard.

var fs = FeatureSetByPortalItem(
    portal,
    '9437842bc29b54b07993a9b6b3d63c0d1"',
    0,
    [
        'InitiateDate',
        'DateWoClosed'
    ],
    false
);
     
var startDate = 'InitiateDate'
var endDate = 'DateWoClosed'
var calcDate = DateDiff(EndDate, start_Date, 'hours')

         
var DiffDate = {
    'fields': [{'name':'calc_date', 'type':'esriFieldTypeDouble'},
               {'name':'start_date', 'type':'esriFieldTypeDate'}
               {'name':'end_date', 'type':'esriFieldTypeDate'}],
    'geometryType': '',
    'features':
    [{'attributes':
     {'start_date': startDate,
     'end_date': endDate,
     'calc_Date': calcDate
     }}]};

return FeatureSet(DiffDate);

[KenBuja]

If you trying to calculate the average for all the records in the FeatureSet, you'll have to loop through the collection to get that average.

var fs = FeatureSetByPortalItem(
    portal,
    '9437842bc29b54b07993a9b6b3d63c0d1"',
    0,
    [
        'InitiateDate',
        'DateWoClosed'
    ],
    false
);
var sumDiff;
for (var f in fs) {
  var startDate = f.InitiateDate
  var endDate = f.DateWoClosed
  sumDiff += DateDiff(EndDate, startDate, 'hours')
}
         
var DiffDate = {
    'fields': [{'name':'calc_date', 'type':'esriFieldTypeDouble'}],
    'geometryType': '',
    'features':
    [{'attributes':
     {
     'calc_Date': sumDiff/Count(fs)
     }}]};

return FeatureSet(DiffDate);

[BNix_TPA]

I don't really need the average calculated for all of the data since I can do this within the indicator itself.  I just want to add a field showing the hours to close to the data within the dashboard so I can display this in the indicator.  I still want the ability to filter based on other fields in the data too after this field is calculated.

[KenBuja · Accepted solution]

This will create a FeatureSet that contains the DateDiff calculation for each record.

var fs = FeatureSetByPortalItem(
  portal,
  9437842bc29b54b07993a9b6b3d63c0d1
  0,
  ['*'], //or use the only the fields you want to include
  false
);

var s = Schema(fs);
//return s
Push(s.fields, {'name':'CloseTime', 'alias': 'Close Time', 'type': 'esriFieldTypeDouble'})

var temp_dict = {
  fields: s['fields'],
  geometryType: '',
  features: []
}

for (var f in fs) {
  console(f)
  var attrs = {}
  for (var attr in f) {
    attrs[attr] = Iif(TypeOf(f[attr]) == 'Date', Number(f[attr]), f[attr])
  }
  console(attrs)
  attrs['CloseTime'] = DateDiff(f.DateWoClosed, f.InitiateDate, 'hours')
  Push(
    temp_dict['features'],
    {attributes: attrs}
  )
}
return FeatureSet(Text(temp_dict))

[BNix_TPA]

Thank you Ken.  I inputted this into the data expression and received an initial error.  I updated the beginning of the code to look like below, but I'm still receiving an error saying unable to execute.

var p = Portal("https://arcgis.com");
var fs = FeatureSetByPortalItem(
  p,
  "9437842bc29b54b07993a9b6b3d63c0d1",
  0,
  ['*'], //or use the only the fields you want to include
  false
);

[KenBuja]

Make sure you have the correct item ID. When I try to open it, I get the message "The item you requested cannot be found. The item may have been deleted or you may have entered an incorrect URL."

[BNix_TPA]

Thank you so much for your help!  This worked.  I realized the issues I was having was due to the item ID and I had the wrong sublayer.  I'm pulling in a feature service from Cityworks and assumed it was sublayer 0, but it was actually 2. But it works perfectly, thank you!

[BNix_TPA]

Hello again, thank you for you help on this.  I'm trying to do a similar thing again as this, but this time I want to calculate the difference between two numbers instead of dates.  Do you know how to alter this code to make that work?