Arcade Group by Day

[JasonJordan00]

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

[JohannesLindner · Accepted solution]

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.

[JasonJordan00]

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

[jcarlson]

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.

[JohannesLindner]

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.

[jcarlson]

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