topic Re: Arcade Group by Day in ArcGIS Online Questions

Arcade Group by Day

JasonJordan00 — Mon, 15 Aug 2022 21:27:51 GMT

I am trying to come up with a data expression to count how many site visits occurred on each day (using created_date). The normal GroupBy function on the field groups it down to the minute, but I need the day. I've tried doing this but it has no effect. Any ideas?

var daily_visits = GroupBy(fs, [{ name: 'date', expression: Text('created_date','Y-MM-DD')}], [{name: 'total', expression: '1', statistic: 'COUNT'}])

The end goal here is to create a serial chart with cumulative visit numbers over time. I do have a working expressions for that however with the number of records it loads very slowly so I'm trying to group them together before it cycles through and does its counting.

Thanks

Re: Arcade Group by Day

JohannesLindner — Tue, 16 Aug 2022 12:29:05 GMT

GroupBy() takes an SQL92 expression, not an Arcade expression.

There are ways to group by day in SQL, but Arcade didn't like any of them, Group By seems to be very limited.

Here's what I came up with in the end:

// load feature set var fs = FeatureSetByPortalItem(Portal(url), id, layer, ["created_date"], false) // Convert datetime to date var fs_date = { geometryType: "", fields: [ {name: "DateShort", type: "esriFieldTypeDate"}, ], features: [] } for(var f in fs) { var d = f.created_date var date_short = Number(Date(Year(d), Month(d), Day(d))) Push(fs_date.features, {attributes: {DateShort: date_short}}) } // group by and order by DateShort var fs_grouped_by_date = GroupBy(FeatureSet(Text(fs_date)), "DateShort", {name: "Total", expression: "1", statistic: "COUNT"}) var fs_ordered_by_date = OrderBy(fs_grouped_by_date, "DateShort") // get cumulative count var fs_cumulative = { geometryType: "", fields: [ {name: "DateShort", type: "esriFieldTypeDate"}, {name: "Total", type: "esriFieldTypeInteger"}, {name: "Cumulative", type: "esriFieldTypeInteger"}, ], features: [] } var cumulative = 0 for(var f in fs_ordered_by_date) { cumulative += f.Total var new_feature = {attributes: {DateShort: Number(f.DateShort), Total: f.Total, Cumulative: cumulative}} Push(fs_cumulative.features, new_feature) } // return return FeatureSet(Text(fs_cumulative))

It loads reasonably fast for my 4.5k features, your mileage may vary...

If you publish from an enterprise gdb, it might be better to do the grouping in a database view with SQL and publish that view.

Re: Arcade Group by Day

JasonJordan00 — Tue, 16 Aug 2022 12:51:27 GMT

Brilliant! I figured it needed something SQL rather than Arcade in the GroupBy. This is just the trick I needed, thanks!

Re: Arcade Group by Day

jcarlson — Tue, 16 Aug 2022 13:15:51 GMT

Also worth noting, that within the GroupBy SQL statement, you could still do a per-date grouping like this:

// get featureset var fs = FeatureSetByPortalItem( Portal('your portal url'), 'itemid of your feature service', 0, // or whatever layer index you need ["created_date"], false ) var fs = FeatureSetByName($datastore,"Building_Footprints") // group featureset by date var grouped = GroupBy( fs, {name: 'datestring', expression: "EXTRACT(YEAR FROM LASTUPDATE) + '-' + EXTRACT(MONTH FROM LASTUPDATE) + '-' + EXTRACT(DAY FROM LASTUPDATE)"}, {name: 'row_count', expression: '1', statistic: 'COUNT'} ) // then do whatever else you need with that grouped featureset

Any time you can offload something to SQL, you'll benefit from it. Doing another for loop and per-feature build of a FeatureSet will still work, but is comparatively costly. Testing this against a 68k-row layer ran in under a second.

Re: Arcade Group by Day

JohannesLindner — Tue, 16 Aug 2022 13:28:57 GMT

See, I tried to use the SQL expression, but I tried things like CASTing to Date or using YEAR(created_Date), MONTH, and DAY, and they all failed.

Didn't try EXTRACT. Glad to see that it does work, if you take the right approach.

Re: Arcade Group by Day

jcarlson — Tue, 16 Aug 2022 14:03:56 GMT

Yeah, CAST only works for numbers in Arcade, which is a bit of an inconvenience.