Data expression to count attributes in multiple fields

[Eimear]

Hi everyone,

I'm new to data expressions and I'm having some trouble writing an expression to create a table in an AGOL dashboard. I need to write an expression that will count the occurrence of "Non_compliant" in a few fields of my  FeatureLayer. See screenshot of 

I'm trying to produce a table like below in the dashboard:

Any help or advice would be greatly appreciated?

[jcarlson]

You can do this with GroupBy, which outputs a FeatureSet, so it's nice and easy.

var fs = FeatureSetByPortalItem(
  Portal('your portal url'),
  'itemid of layer',
  0,
  ['Pitch', 'ClubHouse', 'CarPark']
  false
)

return GroupBy(
  fs,
  {name: 'noncomp', expression: "'Non-Compliant'"},
  [
    {name: 'Pitch', expression: "case when Pitch = 'Non_compliant' then 1 else 0", statistic: 'SUM'},
    {name: 'ClubHouse', expression: "case when ClubHouse = 'Non_compliant' then 1 else 0", statistic: 'SUM'},
    {name: 'CarPark', expression: "case when CarPark = 'Non_compliant' then 1 else 0", statistic: 'SUM'}
  ]
)

I'm not 100% sure the second parameter there will work, but if it does, this would be simplest. You could alternatively, build your FeatureSet from scratch, but you'd need to do three separate Sum functions for that.

[Eimear]

Thanks so much Josh! I really appreciate your help. I got it working, with an additional filter for a district field (see image below).

One additional issue I am having is that I want the resulting table on the dashboard to change/filter based on the user selecting  month for another chart. For this I presume I need to include the DateTime field where inspectors enter the survey date. I've tried adding the "collection_date" field to the returned FeatureSet but no joy. Could you advise if this is possible?

[jcarlson · Accepted solution]

Yes, you'd need to include dates in your data for any filters to work. Using GroupBy is going to eliminate any fields not in the grouping or calculated fields. In order to include the date, your output FeatureSet actually can't be aggregated at all.

That's not necessarily a problem, though, depending on how you're using the output. If you change the second parameter of your GroupBy to be a list like this:

return GroupBy(
  fs,
  [
    {name: 'NonComp', expression: "'Non_compliant'"},
    {name: 'collection_date', expression: 'collection_date'}
  ],
  // etc
)

The resulting FeatureSet will have your non-compliant counts per date.

Now, if you were to bring this Data Expression into something like an Indicator, Chart, or Table widget, the widget itself can further aggregate the data, so you can still get the full total, but the underlying layer will have a Date field baked in for filtering.

[Eimear]

That worked perfectly Josh! Thanks so much. You saved me pulling my hair out for another day! 🙂 

Eimear