Arcade: Count days of data collection with Survey123 field app
2064
13
08-09-2022 02:43 PM
by
by
Sorry, completely forgot to answer you. I didn't mean to let you hanging...
The $datapoint global is only available in the advanced formatting of indicators. What exactly are you trying to do?
Here is an example of what you could do.
Data expression:
// What is your datetime field?
var datetime = "Zeitstempel"
// load data
var p = Portal("...")
var id = "82c62b3205874b139191f6ca0db43ac3"
var lyr = 1
var layer = FeaturesetByPortalItem(p, id, lyr, [datetime], false)
// get first and last datetime
var first_datetime = First(OrderBy(layer, datetime))[datetime]
var last_datetime = First(OrderBy(layer, datetime + " DESC"))[datetime]
// convert to date and get number of days
var first_date = Date(Text(first_datetime, "Y-MM-DD"))
var last_date = Date(Text(last_datetime, "Y-MM-DD"))
var days = DateDiff(last_date, first_date, "days") + 1
// group the featureset by date, get count
// convert to text to remove time
var extract_date_string = `CONCAT(EXTRACT(YEAR FROM ${datetime}), '-', EXTRACT(MONTH FROM ${datetime}), '-', EXTRACT(DAY FROM ${datetime}))`
var grouped_by_date = GroupBy(layer,
[{name: "DateString", expression: extract_date_string}],
[{name: "Count", expression: "1", statistic: "COUNT"}])
// create output featureset
var output_fs = {
fields: [
{name: "Date", type: "esriFieldTypeDate"},
{name: "Responses", type: "esriFieldTypeInteger"},
{name: "Weekday", type: "esriFieldTypeString"},
],
geometryType: "",
features: []
}
// fill the output featureset
for(var row in grouped_by_date) {
var date_parts = Split(row.DateString, "-")
var current_date = Date(date_parts[0], date_parts[1]-1, date_parts[2])
var new_feature = {
attributes: {"Date": Number(current_date), "Responses": row.Count, "Weekday": Text(current_date, "dddd")}
}
Push(output_fs.features, new_feature)
}
// return
Featureset(Text(output_fs))
Use that Data Expression in a pie chart (show percentage of responses on each weekday):
by
The Filter() function doesn't find anything. I tested with dates without time, so searching for "DateField = @current_date" returned values. If you use a datetime field, that won't work. But of course, datetime fields will be the majority of cases, so I reworked the script.
The main difference: convert the datetime to date, change the Filter() expression:
// What is your datetime field?
var datetime = "Zeitstempel"
// get first and last datetime
var first_datetime = First(OrderBy($layer, datetime))[datetime]
var last_datetime = First(OrderBy($layer, datetime + " DESC"))[datetime]
// convert to date and get number of days
var first_date = Date(Text(first_datetime, "Y-MM-DD"))
var last_date = Date(Text(last_datetime, "Y-MM-DD"))
var days = DateDiff(last_date, first_date, "days") + 1
var workdays = 0
var days_without_response = 0
var weekday_bins = [0, 0, 0, 0, 0, 0, 0]
var current_date = first_date
for(var i = 0; i < days; i++) {
var next_date = DateAdd(current_date, 1, "days")
var sql = `${datetime} >= @current_date AND ${datetime} < @next_date`
var responses = Count(Filter($layer, sql))
var iso_day = IsoWeekday(current_date)
weekday_bins[iso_day-1] += responses
workdays += (iso_day < 6)
days_without_response += (responses == 0)
current_date = next_date
}
var return_lines = [
`The survey was running from ${Text(first_date, "Y-MM-DD")} to ${Text(last_date, "Y-MM-DD")} (${days} days, ${workdays} work days).`,
`In this time, ${Sum(weekday_bins)} responses were submitted. There were ${days_without_response} days without response.`,
`Mo: ${weekday_bins[0]}`,
`Tu: ${weekday_bins[1]}`,
`We: ${weekday_bins[2]}`,
`Th: ${weekday_bins[3]}`,
`Fr: ${weekday_bins[4]}`,
`Sa: ${weekday_bins[5]}`,
`Su: ${weekday_bins[6]}`,
]
return Concatenate(return_lines, TextFormatting.NewLine)
I tested this with a layer that spans 15 years. The for loop took way too long, so I used a GroupBy to get the count of responses on each distinct date. That massively shortens the for loop but it means that we have to calculate the workdays instead of counting them.
// What is your datetime field?
var datetime = "Zeitstempel"
// get first and last datetime
var first_datetime = First(OrderBy($layer, datetime))[datetime]
var last_datetime = First(OrderBy($layer, datetime + " DESC"))[datetime]
// convert to date and get number of days
var first_date = Date(Text(first_datetime, "Y-MM-DD"))
var last_date = Date(Text(last_datetime, "Y-MM-DD"))
var days = DateDiff(last_date, first_date, "days") + 1
// calculate work days
// can be approximated with Round(days/7*5), but this can be off by several days
var workdays = 0
// get first monday, add "surplus" work days
var first_monday = first_date
var first_weekday = IsoWeekday(first_date)
if(first_weekday > 1) {
first_monday = DateAdd(first_date, 8 - first_weekday, "days")
workdays += Max(6 - first_weekday, 0)
}
// get last monday, add "surplus" work days
var last_monday = last_date
var last_weekday = IsoWeekday(last_date)
if(last_weekday > 1) {
last_monday = DateAdd(last_date, -last_weekday + 1, "days")
workdays += Min(last_weekday - 1, 4)
}
// add 5 workdays for every week, add the last monday
workdays += DateDiff(last_monday, first_monday, "days") / 7 * 5 + 1
// group the featureset by date, get count
// convert to text to remove time
var extract_date_string = `CONCAT(EXTRACT(YEAR FROM ${datetime}), '-', EXTRACT(MONTH FROM ${datetime}), '-', EXTRACT(DAY FROM ${datetime}))`
var grouped_by_date = GroupBy($layer,
[{name: "DateString", expression: extract_date_string}],
[{name: "Count", expression: "1", statistic: "COUNT"}])
// loop through the grouped records and fill weekday bins
var weekday_bins = [0, 0, 0, 0, 0, 0, 0]
for(var row in grouped_by_date) {
var date_parts = Split(row.DateString, "-")
var current_date = Date(date_parts[0], date_parts[1]-1, date_parts[2])
var iso_day = IsoWeekday(current_date)
weekday_bins[iso_day-1] += row.Count
}
// get total responses and days without response
var responses = Sum(weekday_bins) // same as Count($layer)
var days_without_response = days - Count(grouped_by_date)
// return
var return_lines = [
`The survey was running from ${Text(first_date, "Y-MM-DD")} to ${Text(last_date, "Y-MM-DD")} (${days} days, ${workdays} work days).`,
`In this time, ${responses} responses were submitted. There were ${days_without_response} days without response.`,
`Mo: ${weekday_bins[0]}`,
`Tu: ${weekday_bins[1]}`,
`We: ${weekday_bins[2]}`,
`Th: ${weekday_bins[3]}`,
`Fr: ${weekday_bins[4]}`,
`Sa: ${weekday_bins[5]}`,
`Su: ${weekday_bins[6]}`,
]
return Concatenate(return_lines, TextFormatting.NewLine)
by
by
by
Everything you need is actually already in my scripts, we just need to rearrange a little. You could do something like this:
// What is your datetime and name field?
var datetime = "AngelegtAm"
var name = "AngelegtVon"
// function to get mean edits per day over the whole period
function total(fs, user) {
var first_datetime = First(OrderBy(fs, datetime))[datetime]
var last_datetime = First(OrderBy(fs, datetime + " DESC"))[datetime]
var first_date = Date(Text(first_datetime, "Y-MM-DD"))
var last_date = Date(Text(last_datetime, "Y-MM-DD"))
var days = DateDiff(last_date, first_date, "days") + 1
var edits = Count(Filter(fs, `${name} = @user`))
//return edits / days
return {edits: edits, days: days, mean: edits/days}
}
// function to get mean edits per day where the user actually edited
function specific(fs, user) {
var fs = Filter(fs, `${name} = @user`)
var extract_date_string = `CONCAT(EXTRACT(YEAR FROM ${datetime}), '-', EXTRACT(MONTH FROM ${datetime}), '-', EXTRACT(DAY FROM ${datetime}))`
var grouped_by_date = GroupBy(fs,
[{name: "DateString", expression: extract_date_string}],
[{name: "Count", expression: "1", statistic: "COUNT"}])
var edits = Sum(grouped_by_date, "Count")
var days = Count(grouped_by_date)
//return edits / days
return {edits: edits, days: days, mean: edits/days}
}
To show the difference between the functions:
// Load data
var p = Portal("...")
var id = "b19c6cfbacaf46489495b5b896ad9490"
var lyr = 16
var layer = FeaturesetByPortalItem(p, id, lyr, [datetime, name], false)
layer = Filter(layer, `${name} IS NOT NULL AND ${datetime} IS NOT NULL`)
// get users
var users = Distinct(layer, name)
// create output featureset
var output_fs = {
fields: [
{name: "User", type: "esriFieldTypeString"},
{name: "TotalDays", type: "esriFieldTypeInteger"},
{name: "SpecDays", type: "esriFieldTypeInteger"},
{name: "Edits", type: "esriFieldTypeInteger"},
{name: "TotalMean", type: "esriFieldTypeDouble"},
{name: "SpecMean", type: "esriFieldTypeDouble"},
],
geometryType: "",
features: []
}
// fill the output featureset
for(var user in users) {
var total_mean = total(layer, user[name])
var spec_mean = specific(layer, user[name])
var new_feature = {
attributes: {
User: user[name],
Edits: total_mean.edits,
TotalDays: total_mean.days,
SpecDays: spec_mean.days,
TotalMean: total_mean.mean,
SpecMean: spec_mean.mean
}}
Push(output_fs.features, new_feature)
}
// return
Featureset(Text(output_fs))
