Select to view content in your preferred language

Data Expression to bin years into groups for Serial Chart

1493
6
09-20-2023 12:49 PM
Labels (1)
AustinCanty1
Regular Contributor

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;

 

0 Kudos
6 Replies
jcarlson
MVP Esteemed Contributor

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"}
)

 

- Josh Carlson
Kendall County GIS
AustinCanty1
Regular Contributor

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?

 

0 Kudos
jcarlson
MVP Esteemed Contributor

What's the actual data table look like?

- Josh Carlson
Kendall County GIS
0 Kudos
AustinCanty1
Regular Contributor

@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

0 Kudos
jcarlson
MVP Esteemed Contributor

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:

jcarlson_0-1695738281103.png

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

- Josh Carlson
Kendall County GIS
0 Kudos
AustinCanty1
Regular Contributor

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?

0 Kudos