topic Re: How to calculate a field based on a rolling date range? in ArcGIS Online Developers Questions

How to calculate a field based on a rolling date range?

HeathAnderson — Tue, 07 Jul 2020 20:19:46 GMT

I have a arcade formula that gets a the previous 7 days date but how do I sum the values of a field based on the rolling date range? Xander Bakker‌

var startDate = Date($feature.ReportDate);
var oneWeekBefore = DateAdd(startDate, -7, 'days');

IIf(($feature.ReportDate <= startDate && $feature.ReportDate > oneWeekBefore), Sum($feature.Active), 0);

Re: How to calculate a field based on a rolling date range?

XanderBakker — Sat, 11 Dec 2021 20:22:14 GMT

Hi heathmanderson ,

I haven't tested it, but could you try this?

// define dates
var dateFormat = "MM/DD/Y"; // 12/31/2019
var dateTo = Text(Date($feature.ReportDate), dateFormat);
var dateFrom = Text(DateAdd($feature.ReportDate, -7, 'days'), dateFormat);

// define query and apply filter
var sql = "ReportDate BETWEEN date '" + dateFrom + "' and date '" + dateTo + "'";
var fs = Filter($layer, sql); 

// summarize values in field Active for selected features
return Sum(fs, "Active");
‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

Re: How to calculate a field based on a rolling date range?

XanderBakker — Thu, 09 Jul 2020 17:13:41 GMT

Hi Heath Anderson ,

I just wanted to add that normally when the rolling sum does not have the number of records available (like for the first records) it would return NoData. In this case it might be correct since before the first record the number is probably 0. If this is not the case but you still need some value that could be compared to the other records, you could divide by the number of records that you have available and multiply by 7. It is just a thought, and it depends on the data and what it represents to determine if this is valid.

Re: How to calculate a field based on a rolling date range?

HeathAnderson — Sat, 11 Dec 2021 20:22:16 GMT

Thank you Xander Bakker‌ this is exactly what I was looking for. I modified the script to account for the current day and the previous 6 days. Here it is. Again, thank you.

// Write a script to return a value to show in the pop-up.
// For example, get the average of 4 fields:
// Average($feature.SalesQ1, $feature.SalesQ2, $feature.SalesQ3, $feature.SalesQ4)

// define dates
var dateFormat = "MM/DD/Y"; // 12/31/2019
var dateTo = Text(DateAdd($feature.ReportDate, 1, 'days'), dateFormat);
var dateFrom = Text(DateAdd($feature.ReportDate, -6, 'days'), dateFormat);
console("Report Date between " + dateFrom + ' and date ' + dateTo );


// define query and apply filter
var sql = "ReportDate BETWEEN date '" + dateFrom + "' and date '" + dateTo + "'";
var fs = Filter($layer, sql); 

// summarize values in field Positive for selected features
return Sum(fs, "Positive");‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

Re: How to calculate a field based on a rolling date range?

XanderBakker — Fri, 10 Jul 2020 18:44:46 GMT

Hi heathmanderson ,

I am glad you made it work. Thanks for sharing back your solution.