30 day rolling average ... Best way?

[TonyStrothers]

Asking for the best way to accomplish this. Here's what we are doing. We have a survey that is used twice daily for water calculations. The field personnel would like to see a 30 day rolling average of what has been collected. Also if no collection is done we need to know that it's actually a null value day. Example: So if we collected Sunday through Wednesday, skipped Thursday, and collected for the next 26 days you'd actually have 30 out of 31 days of collection. But since we did not collect on that Thursday, the first day I mentioned in the example (Sunday) would be outside of the 30 day rolling calculation, so it wouldn't be included. But now we have 29 out of 30 (Monday through Wednesday, a skipped Thursday, and 26 days straight starting on that Friday). So the final calculation for our 30 day rolling average would need to readjust itself to take everything, add it together, and divide by 29 and not 30 when skips happen. Most of the time it will be nonstop. That's what they're supposed to do, but I want that safeguard just in case there is the chance of a day being skipped. Would anyone at Esri know how to accomplish this? The most important part is how to track a 30 day rolling average on Survey123 collected data. Is Operations Dashboard the only viable option?

#30 Day #Rolling Average

[JamesTedrick · Accepted solution]

Hi Tony,

How is the data organized?  If the inspection information is in a related table (modeled as a repeat in the form), then this could be possible when opening a survey from the inbox.  In the repeat's question, you would need to specify a query to retrieve the last 30 days of records in the bind::esri:parameters cell ( query="InspectionDate BETWEEN CURRENT_TIMESTAMP - 30 and CURRENT_TIMESTAMP" ) see Standardized SQL functions in ArcGIS Online—ArcGIS Online Help | ArcGIS for details on this query and Prepare for editing existing survey data—Survey123 for ArcGIS | ArcGIS for setting up repeats.  

You would then have a field in the form (outside the repeat section) that would have sum(${value}) div count(${value}) to calculate the average - this will automatically account for empty entries.  See Repeats—Survey123 for ArcGIS | ArcGIS for information on sum() and count().

[TonyStrothers]

Thanks James that seems like it might just work. I’ll let you know for sure next week.