Arcade expression in dashboard serial chart to group inspection dates

[cat206]

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]

Grouping by date in a chart won't give you nice categories like that, so you'd need some other way to get the categories. A Data Expression could do it, but why not just have a couple of Indicators? You can use the widget's built-in filtering to set a specific "before April 1st" date to get your "to be inspected" count, and filter for "on or after April 1" to get your "completed" count.

[cat206]

Thanks for the response Josh, I like the idea but indicators don't give the users ability to be interactive with the map like serial charts or tables do. Our audience likes to be able to click on something to filter out those that are completed and those that are not on the map, so i'm looking for either a table or serial chart to show the counts and interactive

[jcarlson]

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

[cat206]

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]

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