Creating a new feature value in ArcGIS Dashboards based on populated attributes

[PeterDouglassEPD]

I'm having some issues with an arcade expression. I would like to create a feature layer that returns a single value based on the sum of values where if an attribute is populated, it returns 1. There are 3 attributes that need to be scrutinised, thus, for the new feature layer to be presented in the Gauge widget, 100% would equal 3, due to the 3 attributes being populated.

// Reference the FeatureSet by Portal Item

var fs = FeatureSetByPortalItem(

  epportal,

  '*portal item ID redacted*', // Replace with your portal item ID

  0, // Layer index

  [

    'LoADateSigned',

    'HoTsDateSigned',

    'GridApplicationSubmissionDate'

  ],

  false

);

// Initialize the count of populated attributes

var populatedCount = 0;

// Get the first feature (assuming you need one feature)

var featuredata = First(fs);

// Check if the feature exists

if (!IsEmpty(featuredata)) {

  // Check each attribute: Add 1 if populated

  if (!IsEmpty(featuredata.LoADateSigned)) {

    populatedCount += 1;

  }

  if (!IsEmpty(featuredata.HoTsDateSigned)) {

    populatedCount += 1;

  }

  if (!IsEmpty(featuredata.GridApplicationSubmissionDate)) {

    populatedCount += 1;

  }

}

// Return the total sum of populated attributes

return populatedCount;

[jcarlson · Accepted solution]

Summing fields could be done with a nice GroupBy, avoiding a time-consuming loop. A Data Expression has to return a FeatureSet, not simply a number, too.

// the featureset
var fs = FeatureSetByPortalItem(
  // your code here
)

// SQL expression to count populated fields
var sql = `CASE WHEN LoADateSigned IS NOT NULL THEN 1 ELSE 0 END +
CASE WHEN HoTsDateSigned IS NOT NULL THEN 1 ELSE 0 END +
CASE WHEN GridApplicationSubmissionDate IS NOT NULL THEN 1 ELSE 0 END`


// group it, sum the three fields if not empty
return GroupBy(
  fs,
  'objectid',
  {
    name: 'populatedCount',
    expression: sql,
    statistic: 'SUM'
  }
)

[PeterDouglassEPD]

Thanks Josh, this has worked well, only issue is that I can't apply category selectors to scrutinise the data, is there a quick way of being able to incorporate this?

[PeterDouglassEPD]

Just worked it out, thank you so much Josh!

[jcarlson]

Sure! In order to interact with your Data Expression, we have to make sure that every field you need is included in the list of grouping fields.

return GroupBy(
  fs,
  [
    'objectid',
    'some_category',
    'some_other_category'
  ],
  {
    name: 'populatedCount',
    expression: sql,
    statistic: 'SUM'
  }
)

Then your other attributes will be in the output for interactions.