Sum Attribute Expression

Question

Hey All-

Apologize for the Arcade (and coding in general) noob question, but I am trying to figure out how to sum up the results of an attribute expression. Here's the scoop, the attribute expression is a polynomial expression that yields the stormwater benefits in dollars of a tree based on the trunk diameter in an expression popup field called StormwaterBenefits:

var SB = (0.0921*(pow($feature.DBH,2)))+(1.5627*$feature.DBH)-3.2441

No big deal, right? But what I want to do is know the sum total of all trees in the population of around 10,000 trees. Seems like I should be able to just do something simple like the following:

var SumSB = Sum($layer, 'StormwaterBenefits')

return '$'+text (SumSB, '#,###.##')

But Arcade won't accept the attribute expression result name as a valid field name as input to another expression. I feel like I am missing something REALLY obvious here. I have a few workarounds in mind, but I am really bent on figuring this out. Again, very little coding experience, so please speak slowly and use small words!

Thanks!

jcarlson · Answer

When you look at how feature layers are brought into a map and accessed from the REST endpoint, you'll find that it's building a SQL query to submit to the server / database. Even though what you are doing / writing may not itself be SQL, that doesn't mean it isn't using SQL.

In Arcade, many of the functions that work with data at the "layer" level, rather than on single features, may utilize SQL.

* GroupBy
* Filter
* Distinct
* OrderBy
* All the statistical expressions (min, max, sum, mean, etc)

To be clear, you're still writing an Arcade expression. It's just that one of the parameters of the Sum function happens to be string of text representing a SQL expression.

Steve_Lane · Answer

Josh-

Thanks for the info, this is great! I was actually pretty close on this, but it begs the question, why the need to write it in SQL for the Sum and not just reuse the original Arcade expression? I assume this would violate some syntactical principle I am as yet unaware of...

Cheers

Steve

jcarlson · Answer

It's true, you can't reference one expression's output from another. So how do you pass StormwaterBenefits into the Sum function?

If you look at the docs for the Sum function, you'll see that instead of a field, it can also take a SQL92 expression. That's really what you need here.

Your Arcade expression for StormwaterBenefits,

(0.0921 * (pow($feature.DBH, 2))) + (1.5627 * $feature.DBH) - 3.2441

convered to SQL would look like this:

0.0921 * POWER(DBH, 2) + 1.5627 * DBH - 3.2441

Worth noting, your expression will keep to the order of operations, so the parentheses are not necessary, except for the POWER function.

So in order to get the sum of the StormwaterBenefits, your Arcade expression would look like this:

var SumSB = Sum(
    SB,
    '0.0921 * POWER(DBH, 2) + 1.5627 * DBH - 3.2441'
)

return Text(SumSB, '$#,###.##')

Note here that you can move the currency symbol inside of the formatting string on Text.

Here's a test output from the Arcade Playground's sample layer: