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

270
4
07-07-2020 01:19 PM
Occasional Contributor

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

Tags (5)
1 Solution

Accepted Solutions
Esri Esteemed Contributor

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

``// define datesvar dateFormat = "MM/DD/Y"; // 12/31/2019var dateTo = Text(Date(\$feature.ReportDate), dateFormat);var dateFrom = Text(DateAdd(\$feature.ReportDate, -7, 'days'), dateFormat);// define query and apply filtervar sql = "ReportDate BETWEEN date '" + dateFrom + "' and date '" + dateTo + "'";var fs = Filter(\$layer, sql); // summarize values in field Active for selected featuresreturn Sum(fs, "Active");‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍``
4 Replies
Esri Esteemed Contributor

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

``// define datesvar dateFormat = "MM/DD/Y"; // 12/31/2019var dateTo = Text(Date(\$feature.ReportDate), dateFormat);var dateFrom = Text(DateAdd(\$feature.ReportDate, -7, 'days'), dateFormat);// define query and apply filtervar sql = "ReportDate BETWEEN date '" + dateFrom + "' and date '" + dateTo + "'";var fs = Filter(\$layer, sql); // summarize values in field Active for selected featuresreturn Sum(fs, "Active");‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍``
Esri Esteemed Contributor

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.

Occasional Contributor

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 datesvar dateFormat = "MM/DD/Y"; // 12/31/2019var 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 filtervar sql = "ReportDate BETWEEN date '" + dateFrom + "' and date '" + dateTo + "'";var fs = Filter(\$layer, sql); // summarize values in field Positive for selected featuresreturn Sum(fs, "Positive");‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍``
Esri Esteemed Contributor