topic Re: FeatureSet GroupBy CASE expression in ArcGIS Dashboards Questions

FeatureSet GroupBy CASE expression

BlakeMorrison — Wed, 03 Apr 2024 04:47:28 GMT

I'm trying to write an SQL CASE expression for use within a FeatureSet/GroupBy function. I have a Date field that I'm grabbing using FeatureSetByPortalItem and I want to create a field that just puts the ages in classes.

Ideally, the dates would be dynamic as well, based on today's date and a defined age.

I'm pretty sure the indentation below is improper, but you get the idea of what I'm trying to achieve.

{ name: 'feature_age', expression: 'CASE WHEN date_stat => 2020 THEN 1 ELSE date_stat => 2012 THEN 2 ELSE date_stat => 1984 THEN 3 ELSE date_stat => 1850 THEN 4 ELSE NULL END', statistic: 'SUM' },

Related to a previous post that I found the solution to: https://community.esri.com/t5/arcgis-dashboards-questions/data-too-big-for-a-featureset-calculation-how-else/m-p/1404223#M9338

Re: FeatureSet GroupBy CASE expression

BlakeMorrison — Wed, 03 Apr 2024 07:31:48 GMT

For those playing at home, the answer is:

{ name: 'feature_age', expression: 'CASE WHEN @today-date_stat < 1460 THEN 1 WHEN @today-date_stat < 4380 THEN 2 WHEN @today-date_stat < 14600 THEN 3 WHEN @today-date_stat > 14600 THEN 4 ELSE NULL END', statistic: 'SUM', },

Note: Number of days is used to calculate the age. Ie. 1460 = 4x365

Note2: @today is a var, outside the GroupBy function, defined as Now()

Re: FeatureSet GroupBy CASE expression

KenBuja — Wed, 03 Apr 2024 13:18:51 GMT

You're not accounting for leap days. Four years has 1461 days, 12 years has 4383 days, and 40 years has 14610 days.

Re: FeatureSet GroupBy CASE expression

BlakeMorrison — Thu, 04 Apr 2024 00:19:39 GMT

haha

Thanks!