topic Data Expression to bin years into groups for Serial Chart in ArcGIS Dashboards Questions

Data Expression to bin years into groups for Serial Chart

AustinCanty1 — Wed, 20 Sep 2023 19:49:26 GMT

I'm trying to make a data expression for a serial chart that groups years into categories rather than showing counts for each year. My year field is just stored in an integer field. This is what I've come up with so far but it's not working.

var portal = Portal('https://arcgis.com'); var fs = FeatureSetByPortalItem( portal, 'xxxxxxxxxxxxx', 0, [ 'YearInstall' ], true ); var results = []; for (var i in fs){ var yearInstall = fs[i].YearInstall; var dec = IIf(yearInstall >= 1912 && yearInstall <= 1961, "1", IIf(yearInstall >= 1962 && yearInstall <= 1991, "2", IIf(yearInstall >= 1992 && yearInstall <= 2023, "3", 'other'))); results[i] = dec; } return results;

Re: Data Expression to bin years into groups for Serial Chart

jcarlson — Thu, 21 Sep 2023 13:00:50 GMT

The key thing here is that a Data Expression expects a FeatureSet as its result, where your expression is returning an array of numeric strings.

If you really wanted to do it this way, you have to build an array of Features and add it to a FeatureSet. You can see a lot of examples here: https://github.com/Esri/arcade-expressions/tree/master/dashboard_data

But really, you can do this all with a function that returns a FeatureSet, like GroupBy.

var fs = FeatureSetByPortalItem( Portal('https://arcgis.com'), 'itemid', 0, ['YearInstall'], false ) var sql = `CASE WHEN YearInstall < 1962 THEN 1 WHEN YearInstall < 1992 THEN 2 ELSE 3 END` var grp = GroupBy( fs, {name: 'year_bin', expression: sql}, {name: 'total', expression: '1', statistic: "SUM"} )

Re: Data Expression to bin years into groups for Serial Chart

AustinCanty1 — Thu, 21 Sep 2023 14:24:12 GMT

Wow thanks, Josh!

That seems a lot more efficient, but grp is returning a featureset with just a single row rather than all three. Am I missing something else?

Re: Data Expression to bin years into groups for Serial Chart

jcarlson — Thu, 21 Sep 2023 14:28:11 GMT

What's the actual data table look like?

Re: Data Expression to bin years into groups for Serial Chart

AustinCanty1 — Tue, 26 Sep 2023 14:20:22 GMT

@jcarlson

Year install is an integer that contains some null values. I realize I would have to account for the null values in the function, but that's still only returning one record in the featureset.

I published a copy of the dataset here: https://www.arcgis.com/home/item.html?id=ad72f530682f4a4a94e65ee996c26bb0#data

Re: Data Expression to bin years into groups for Serial Chart

jcarlson — Tue, 26 Sep 2023 14:25:47 GMT

Accounting for nulls:

var fs = Filter( FeatureSetByPortalItem( Portal('https://arcgis.com'), 'ad72f530682f4a4a94e65ee996c26bb0', 0, ['YearInstall'], false ), 'YearInstall IS NOT NULL' ) var sql = `CASE WHEN YearInstall < 1962 THEN 1 WHEN YearInstall < 1992 THEN 2 ELSE 3 END` var grp = GroupBy( fs, {name: 'year_bin', expression: sql}, {name: 'total', expression: '1', statistic: "SUM"} ) return grp

Runs like this:

I don't see the one-record output. Double check your expression for typos?

Re: Data Expression to bin years into groups for Serial Chart

AustinCanty1 — Tue, 26 Sep 2023 15:11:47 GMT

Thanks again. It works with that sample dataset I provided but not with my actual data.

Is there something else that needs to be done to use a secured service with stored credentials?