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

[HeathAnderson]

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

[XanderBakker · Accepted solution]

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

[XanderBakker]

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. 

[HeathAnderson]

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

[XanderBakker]

Hi heathmanderson ,

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