nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Arcade: Count days of data collection with Survey123 field app

FedericoRiet_Sapriza

Hi,

I would like help with Arcade, I'm making a Dashboard and I need to count the days that data was collected with Survey123 and show this in a Indicator.

Example: in a month I may have 27 surveys, that were collected in 16 days. how can I count this with Arcade?

So for instance in a period of 4 months in which the survey was performed I would like to know how many work days and now many days data was not collected

make sense?

Thanks

Cheers,

Federico

@GeeFernando

@JohannesLindner

Find more posts tagged with

Arcade

Accepted answers

All comments

JohannesLindner

I packed it all in one expression, you will probably have to extract this into multiple expressions.

// load your survey
var survey_dict = {geometryType: "", fields: [{name: "DateCollected", type: "esriFieldTypeDate"}], features: [{attributes: {DateCollected: Number(Date(2022,7,1))}},{attributes: {DateCollected: Number(Date(2022,7,1))}},{attributes: {DateCollected: Number(Date(2022,7,2))}},{attributes: {DateCollected: Number(Date(2022,7,3))}},{attributes: {DateCollected: Number(Date(2022,7,3))}},{attributes: {DateCollected: Number(Date(2022,7,4))}},{attributes: {DateCollected: Number(Date(2022,7,4))}},{attributes: {DateCollected: Number(Date(2022,7,4))}},{attributes: {DateCollected: Number(Date(2022,7,6))}},{attributes: {DateCollected: Number(Date(2022,7,6))}},{attributes: {DateCollected: Number(Date(2022,7,8))}},{attributes: {DateCollected: Number(Date(2022,7,8))}},{attributes: {DateCollected: Number(Date(2022,7,10))}},{attributes: {DateCollected: Number(Date(2022,7,11))}},{attributes: {DateCollected: Number(Date(2022,7,11))}},]}
var survey = FeatureSet(Text(survey_dict))
//return survey

//var survey = FeatureSetByName(...)

// optionally, filter the survey
//survey = Filter(survey, "SurveyID IN (1, 2, 3)")

// abort if nothing was found
if(Count(survey) == 0) { return null }

// get first and last date
var first_date = First(OrderBy(survey, "DateCollected")).DateCollected
var last_date = First(OrderBy(survey, "DateCollected DESC")).DateCollected

// get the range in days
var range_days = DateDiff(last_date, first_date, "days") + 1

// get crazy binning the survey resonses into weekdays,
// counting the workdays, and days without response
var weekday_bins = [0, 0, 0, 0, 0, 0, 0]
var workdays = 0
var days_without_response = 0

var current_date = first_date
for(var i = 0; i < range_days; i++) {
    var iso_day = IsoWeekday(current_date)
    var responses = Filter(survey, "DateCollected = @current_date")
    weekday_bins[iso_day-1] += Count(responses)
    workdays += (iso_day < 6)
    days_without_response += (Count(responses) == 0)
    current_date = DateAdd(current_date, 1, "days")
}

var return_lines = [
    `The survey was running from ${Text(first_date, "Y-MM-DD")} to ${Text(last_date, "Y-MM-DD")} (${range_days} days, ${workdays} work days).`,
    `In this time, ${Count(survey)} 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)

The survey was running from 2022-08-01 to 2022-08-11 (11 days, 9 work days).
In this time, 15 responses were submitted. There were 3 days without response.
Mo: 4
Tu: 1
We: 3
Th: 5
Fr: 0
Sa: 2
Su: 0

FedericoRiet_Sapriza

Awesome, I'll try this and let you know

YOU ROCK!!!

FedericoRiet_Sapriza

Hi Johannes,

I have been working on expression and I encounter some issues,

I don't have the functions:

"FeatureSetByName"

or the

"Filter"

these are not available for me, I'm also using the $datapoint.datecollected.

Nevertheless, still not being able not solve this issue.

Thanks again for your time.

Cheers,

Federico

MosquitoGIS

This is freaking awesome! Thank you so much for building and sharing this. It has helped me out immensely.

I built some things based on what you have here and couldn't get it work.

So, I basically copied and pasted what you did and made minor changes (changed the date field it is referencing, and called the layer to call upon itself instead of a variable.). I am not getting the same results. Something dealing with the 'responses' variable doesn't seem to be working. Any idea what it may be?

a.jpg

JohannesLindner

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)

MosquitoGIS

Excellent! Thank you so much! And I must say your code writing is like an art form! Seriously, incredible!

MosquitoGIS

And I appreciate the other way you posted as well. As I have one I may be running it on one that has a pretty large amount of data.

JohannesLindner

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):

MosquitoGIS

One more quick question if I may ask. I have no problems trying to hash it out, but I figure I would ask in case there is as simple way to do it from what you have already done (as I said, your code writing is like an art form). My ultimate goal is to essentially average out the amount of records a person did a day over a full years worth of data. So, the final result is just one number that shows at the end of the year, this person (based on something like the 'Editor' field) was able to do *said number* a day. Have any suggestions or best practices to pull this off, especially if it was to pull the names dynamically?

Join the Conversation

Signed in members can post, follow updates, and more. New here? Register a free account.

Quick Links

Community Help
Find useful guides, FAQs, and documents to help you navigate and make the most of Esri Community.