Data expression to count attributes in multiple fields

510
4
Jump to solution
10-26-2023 08:47 AM
Labels (1)
Eimear
by
New Contributor II

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 

EimearT_0-1698334270203.png

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

EimearT_1-1698334447506.png

Any help or advice would be greatly appreciated?

0 Kudos
1 Solution

Accepted Solutions
jcarlson
MVP Esteemed Contributor

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.

- Josh Carlson
Kendall County GIS

View solution in original post

0 Kudos
4 Replies
jcarlson
MVP Esteemed Contributor

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.

- Josh Carlson
Kendall County GIS
Eimear
by
New Contributor II

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

EimearT_0-1698764032597.png

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?

 

0 Kudos
jcarlson
MVP Esteemed Contributor

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.

- Josh Carlson
Kendall County GIS
0 Kudos
Eimear
by
New Contributor II

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

Eimear

0 Kudos