Arcade Dashboard - Calculate a number of applications based on a reference date

Question

Hello,

I have a table with this structure.

As an output, I would like to be able to calculate the difference in the number of applications per year.

Knowing that 2021 is my reference year.

By owner I would like to obtain the application delta between 2021 and 2022, between 2021 and 2023. And so on for the years to come.

I have tried a lot of things using dictionaries but without any convincing results.

Can someone help me?

jcarlson · Accepted Answer

It's doable, but involves looping and filtering. With nested filter statements, you'll want to force the FeatureSet into your browser's memory, otherwise you'll end up sending lots of pings to the server.

function Memorize(fs) {
  var temp_dict = {
    fields: Schema(fs)['fields'],
    geometryType: '',
    features: []
  }

for (var f in fs) {
    var attrs = {}

for (var attr in f) {
      attrs[attr] = Iif(TypeOf(f[attr]) == 'Date', Number(f[attr]), f[attr])
    }

Push(
      temp_dict['features'],
      {attributes: attrs}
    )
  }

return FeatureSet(Text(temp_dict))
}

var fs = Memorize(FeatureSetByPortalItem(...))

// create output dictionary
var out_dict = {
  fields: [
    { name: 'owner', type: 'esriFieldTypeString' },
    { name: 'year', type: 'esriFieldTypeInteger' },
    { name: 'application', type: 'esriFieldTypeInteger' },
    { name: 'delta', type: 'esriFieldTypeInteger' }
  ],
  geometryType: '',
  features: []
}

// loop through featureset and populate array
for (var f in fs) {

// get matching 2021 feature
  var ref_point = First(Filter(fs, `year = 2021 and owner = '${f['owner']}'`))

// push feature to array
  Push(
    out_dict['features'],
    { attributes: {
      owner: f['owner'],
      year: f['year'],
      application: f['application'],
      delta: f['application'] - ref_point['application']
    }}
  )

return FeatureSet(Text(out_dict))