Select to view content in your preferred language

Arcade: Count days of data collection with Survey123 field app

2019
13
08-09-2022 02:43 PM
Labels (1)
FedericoRiet_Sapriza
Occasional Contributor

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 

0 Kudos
13 Replies
JohannesLindner
MVP Frequent Contributor

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

Have a great day!
Johannes
FedericoRiet_Sapriza
Occasional Contributor

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

 

0 Kudos
JohannesLindner
MVP Frequent Contributor

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

JohannesLindner_1-1663175206332.png

 


Have a great day!
Johannes
0 Kudos
MosquitoGIS
Frequent Contributor

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?

0 Kudos
JohannesLindner
MVP Frequent Contributor

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)

 

JohannesLindner_0-1663173789027.png


Have a great day!
Johannes
MosquitoGIS
Frequent Contributor

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

0 Kudos
MosquitoGIS
Frequent Contributor

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.

0 Kudos
MosquitoGIS
Frequent Contributor

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?

0 Kudos
JohannesLindner
MVP Frequent Contributor

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

 

JohannesLindner_1-1663220855398.png

 

 


Have a great day!
Johannes