Select to view content in your preferred language

Calculating Difference Between Two Dates using Arcade Expressions

1242
7
Jump to solution
05-21-2024 10:47 AM
BNix_TPA
Emerging Contributor

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);

 

0 Kudos
1 Solution

Accepted Solutions
KenBuja
MVP Esteemed Contributor

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))

 

 

View solution in original post

0 Kudos
7 Replies
KenBuja
MVP Esteemed Contributor

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);
0 Kudos
BNix_TPA
Emerging Contributor

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.

0 Kudos
KenBuja
MVP Esteemed Contributor

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))

 

 

0 Kudos
BNix_TPA
Emerging Contributor

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
);

 

0 Kudos
KenBuja
MVP Esteemed Contributor

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

0 Kudos
BNix_TPA
Emerging Contributor

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!

0 Kudos
BNix_TPA
Emerging Contributor

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?

0 Kudos