Ah, I see. It looks like it's having trouble with the null values. Would it be inaccurate for your survey to default all answers to 0? Try updating the null values to 0 and then run the expression again.
If those aren't options, you can get this to work, but you've got to address those null values.
Instead of just invoking the field name, you'll need to use a case when... then... else... end within the sum_exp. Since GroupBy is submitting an SQL statement to the service, this all works.
For example, instead of emergency_cost_debris + emergency_cost_epm, the expression would read
case when emergency_cost_debris is not null then emergency_cost_debris else 0 end +
case when emergency_cost_epm is not null then emergency_cost_epm else 0 end
But that would be a lot of typing, and a very long expression. One way to shorten this is to use a custom function.
// Auxiliary function for assembling expression
function field_exp(field) {
return `case when ${field} is not null then ${field} else 0 end`
};
This function will take the given string field and return the appropriate SQL statement. Using this, the expression above can be re-written as
`${field_exp('emergency_cost_debris')} + ${field_exp('emergency_cost_epm')`
But even then, that would still end up being quite a long expression. We can do one better by using a loop with a list of field names.
// List of fields to sum
var sum_fields = [
"emergency_cost_debris",
"emergency_cost_epm",
"permanent_cost_rb_fa",
"permanent_cost_rb_nfa",
"permanent_cost_wcf",
"permanent_cost_pbe",
"permanent_cost_pu",
"permanent_cost_prf"
]
// Instantiate sum_exp as empty string
var sum_exp = ''
for (var f in sum_fields) {
sum_exp += `${field_exp(sum_fields[f])} + `
}
This results in the sum_exp value
case when emergency_cost_debris is not null then emergency_cost_debris else 0 end + case when emergency_cost_epm is not null then emergency_cost_epm else 0 end + case when permanent_cost_rb_fa is not null then permanent_cost_rb_fa else 0 end + case when permanent_cost_rb_nfa is not null then permanent_cost_rb_nfa else 0 end + case when permanent_cost_wcf is not null then permanent_cost_wcf else 0 end + case when permanent_cost_pbe is not null then permanent_cost_pbe else 0 end + case when permanent_cost_pu is not null then permanent_cost_pu else 0 end + case when permanent_cost_prf is not null then permanent_cost_prf else 0 end + case when location_county is not null then location_county else 0 end +
Nearly right, but there's the pesky " + " on the final item. Arcade doesn't have a nice "join" function like Python, but we can just trim it off like so.
sum_exp = Left(sum_exp, Count(sum_exp) -3)
An added benefit of this method is that the sum_fields array can be used as a parameter when requesting our FeatureSet. We just need to use Push() to add the county location field prior to the function.
All Together Now!
// List of fields to sum
var sum_fields = [
"emergency_cost_debris",
"emergency_cost_epm",
"permanent_cost_rb_fa",
"permanent_cost_rb_nfa",
"permanent_cost_wcf",
"permanent_cost_pbe",
"permanent_cost_pu",
"permanent_cost_prf"
]
// Auxiliary function for assembling expression
function field_exp(field) {
return `case when ${field} is not null then ${field} else 0 end`
};
// Instantiate sum_exp as empty string
var sum_exp = ''
// Populate expression
for (var f in sum_fields) {
sum_exp += `${field_exp(sum_fields[f])} + `
}
// Trim off last 3 characters
sum_exp = Left(sum_exp, Count(sum_exp) - 3)
// Push county field into fields array
Push(sum_fields, 'location_county')
// Get featureset
var fs = FeatureSetByPortalItem(
Portal('https://arcgis.com'),
'213a9271b441439196855a540ea797bb',
0,
sum_fields,
false);
// Group features by county
var g_fs = GroupBy(
fs,
'location_county',
[ // statistics to return for each county
{ name: 'Total', expression: '1', statistic: 'COUNT' },
{ name: 'catTotal', expression: sum_exp, statistic: 'SUM' }
]
);
return g_fs
Which returns:
The Indicator
Now you have your FeatureSet. The indicator can be set to do the full sum:
Then on a category selector, list widget, etc., you can set a Filter action:
Drum roll, please:
It's alive... it's ALIVE!
- Josh Carlson
Kendall County GIS