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;
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"}
)
What's the actual data table look like?
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
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?
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?