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)
Have a great day!
Johannes