Arcade expression in dashboard serial chart to group inspection dates

Question

Hi,

I have a live join layer (joined view layer) in ArcGIS Online which joins pond inventory data with the condition inspection records. It is a one to one join, descending on the Inspection_Date field, to ensure we only have the latested inspection record. It is a left join so the data includes any ponds that do not have an inspection record in the related table.

I'm looking to create a dashboard to track the condition inspection status. For example, I'm looking at creating a serial chart, grouping all those with the Inspection_Date field prior to 1st April 2024 to be "Inspection yet to be inspected" and any after 1st April 2024 to be "Inspection Completed".

Does anyone know if this is possible?

jcarlson · Answer

Based on your screenshot, it looks like the closing parenthesis is missing from Distinct.

cat206 · Answer

Hi Josh,

Aplogies for my delayed response. Thank you so much for helping me with this. I've given it a go when adding a table but I get this error: Test execution error: ',' expected.. Verify test data.

I can't figure out where the missing ',' should go

jcarlson · Answer

Makes sense. Well, if a Data Expression is an option, you can probably get it to work. There are a couple of approaches people use, but first, get your features:

var fs = FeatureSetByPortalItem(
  Portal('your portal url'),
  'itemid of your service',
  0, // layer index of the feature layer
  ['objectid', 'Inspection_Date'],
  false
)

A common approach from here is to loop through the entire FeatureSet, check the date field, and push features to an output FeatureSet with a new category field in place. By including the objectid, the resulting data can still be used to filter the rest of the dashboard.

It will work, and it's easy to work with Arcade's built-in Date functions. But if you have a lot of features, this can take a lot of time to evaluate.

var out_dict = {
  fields: [
    {name: objectid, type: 'esriFieldTypeOID'},
    {name: inspection_complete, type: 'esriFieldTypeString'}
  ],
  geometryType: '',
  features: []
}

var threshold = Date(2024, 3, 1) // months are 0-indexed, so 3 is april

for (var f in fs) {
  // check date
  var insp_complete = Iif(
    DateDiff(threshold, f['Inspection_Date']) >= 0,
    'Incomplete',
    'Inspection Completed'
  )

// push into array
  Push(
    out_dict['features'],
    { attributes: { objectid: f['objectid'], inspection_complete: insp_complete }}
  )
}

return FeatureSet(Text(out_dict))

Another method is to use GroupBy or Distinct. These functions let you use SQL to create new "fields" in the output, but excecute on the server side, and are much faster. Not as fun to work with timestamps, though. The particular format of the date functions will depend on the database your service is coming from.

var sql = `CASE
WHEN Inspection_Date < TIMESTAMP '2024-04-01 00:00:00'
THEN 'Incomplete'
ELSE 'Inspection Complete'
END`

return Distinct(
  fs,
  [
    { name: 'objectid', expression: 'objectid' },
    { name: 'inspection_complete', expression: sql }
  ]
)

If your system doesn't like the timestamp syntax, you can get away with using EXTRACT most of the time, something like:

CASE
WHEN EXTRACT(MONTH FROM Inspection_Date) >= 4 AND EXTRACT(YEAR FROM Inspection_Date) = 2024
THEN 'Inspection Complete'
ELSE 'Incomplete
END