topic Re: Arcade: Count days of data collection with Survey123 field app in ArcGIS Online Questions

Arcade: Count days of data collection with Survey123 field app

FedericoRiet_Sapriza — Tue, 09 Aug 2022 21:43:22 GMT

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

Re: Arcade: Count days of data collection with Survey123 field app

JohannesLindner — Wed, 10 Aug 2022 08:17:06 GMT

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

Re: Arcade: Count days of data collection with Survey123 field app

FedericoRiet_Sapriza — Mon, 15 Aug 2022 13:54:29 GMT

Awesome, I'll try this and let you know

YOU ROCK!!!

Re: Arcade: Count days of data collection with Survey123 field app

FedericoRiet_Sapriza — Thu, 18 Aug 2022 01:40:05 GMT

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

Re: Arcade: Count days of data collection with Survey123 field app

MosquitoGIS — Tue, 13 Sep 2022 16:40:43 GMT

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?

Re: Arcade: Count days of data collection with Survey123 field app

JohannesLindner — Wed, 14 Sep 2022 16:43:58 GMT

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:

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)

Re: Arcade: Count days of data collection with Survey123 field app

MosquitoGIS — Wed, 14 Sep 2022 17:00:45 GMT

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

Re: Arcade: Count days of data collection with Survey123 field app

MosquitoGIS — Wed, 14 Sep 2022 17:02:25 GMT

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.

Re: Arcade: Count days of data collection with Survey123 field app

JohannesLindner — Wed, 14 Sep 2022 17:07:29 GMT

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

Re: Arcade: Count days of data collection with Survey123 field app

MosquitoGIS — Wed, 14 Sep 2022 17:18:36 GMT

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?

Re: Arcade: Count days of data collection with Survey123 field app

JohannesLindner — Thu, 15 Sep 2022 05:48:09 GMT

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

Re: Arcade: Count days of data collection with Survey123 field app

MosquitoGIS — Thu, 15 Sep 2022 19:14:11 GMT

Thanks, I will be scouring over this code learning. You seriously have been extremely helpful.

One more question for you if you are willing, how is it the you work with or handle nulls?

Re: Arcade: Count days of data collection with Survey123 field app

JohannesLindner — Fri, 16 Sep 2022 11:55:23 GMT

In this example, I excluded records with nulls in the user or date field from the input data, see line 6:

layer = Filter(layer, `${name} IS NOT NULL AND ${datetime} IS NOT NULL`)

As this problem is all about working with dates, we have to exclude null values in the date field. But it could be interesting to know about null values in the user field. For that, we have to change the filter inside the function according to whether the user argument is null or not:

function specific(fs, user) { var sql = `${datetime} IS NOT NULL AND ${name} = @user` if(user == null) { sql = `${datetime} IS NOT NULL AND ${name} IS NULL` } var fs = Filter(fs, sql) 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} }

If you use the editor tracking fields for this, you won't get any meaningful results, because when the user field is empty, the date field will obviously be empty, too.

Re: Arcade: Count days of data collection with Survey123 field app

MosquitoGIS — Fri, 16 Sep 2022 13:33:50 GMT

Awesome, thank you so much!