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

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.