nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

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

Finde mehr Beiträge mit dem Tag

Accepted answers

XanderBakker

Hi heathmanderson ,

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

<SPAN class="comment token">// define dates</SPAN>
<SPAN class="keyword token">var</SPAN> dateFormat <SPAN class="operator token">=</SPAN> <SPAN class="string token">"MM/DD/Y"</SPAN><SPAN class="punctuation token">;</SPAN> <SPAN class="comment token">// 12/31/2019</SPAN>
<SPAN class="keyword token">var</SPAN> dateTo <SPAN class="operator token">=</SPAN> <SPAN class="token function">Text</SPAN><SPAN class="punctuation token">(</SPAN><SPAN class="token function">Date</SPAN><SPAN class="punctuation token">(</SPAN>$feature<SPAN class="punctuation token">.</SPAN>ReportDate<SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">,</SPAN> dateFormat<SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">;</SPAN>
<SPAN class="keyword token">var</SPAN> dateFrom <SPAN class="operator token">=</SPAN> <SPAN class="token function">Text</SPAN><SPAN class="punctuation token">(</SPAN><SPAN class="token function">DateAdd</SPAN><SPAN class="punctuation token">(</SPAN>$feature<SPAN class="punctuation token">.</SPAN>ReportDate<SPAN class="punctuation token">,</SPAN> <SPAN class="operator token">-</SPAN><SPAN class="number token">7</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="string token">'days'</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">,</SPAN> dateFormat<SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">;</SPAN>

<SPAN class="comment token">// define query and apply filter</SPAN>
<SPAN class="keyword token">var</SPAN> sql <SPAN class="operator token">=</SPAN> <SPAN class="string token">"ReportDate BETWEEN date '"</SPAN> <SPAN class="operator token">+</SPAN> dateFrom <SPAN class="operator token">+</SPAN> <SPAN class="string token">"' and date '"</SPAN> <SPAN class="operator token">+</SPAN> dateTo <SPAN class="operator token">+</SPAN> <SPAN class="string token">"'"</SPAN><SPAN class="punctuation token">;</SPAN>
<SPAN class="keyword token">var</SPAN> fs <SPAN class="operator token">=</SPAN> <SPAN class="token function">Filter</SPAN><SPAN class="punctuation token">(</SPAN>$layer<SPAN class="punctuation token">,</SPAN> sql<SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">;</SPAN> 

<SPAN class="comment token">// summarize values in field Active for selected features</SPAN>
<SPAN class="keyword token">return</SPAN> <SPAN class="token function">Sum</SPAN><SPAN class="punctuation token">(</SPAN>fs<SPAN class="punctuation token">,</SPAN> <SPAN class="string token">"Active"</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">;</SPAN>
‍‍‍‍‍‍‍‍‍‍‍<SPAN class="line-numbers-rows"><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN></SPAN>

All comments

XanderBakker

Hi heathmanderson ,

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

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.

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

<SPAN class="comment token">// define dates</SPAN>
<SPAN class="keyword token">var</SPAN> dateFormat <SPAN class="operator token">=</SPAN> <SPAN class="string token">"MM/DD/Y"</SPAN><SPAN class="punctuation token">;</SPAN> <SPAN class="comment token">// 12/31/2019</SPAN>
<SPAN class="keyword token">var</SPAN> dateTo <SPAN class="operator token">=</SPAN> <SPAN class="token function">Text</SPAN><SPAN class="punctuation token">(</SPAN><SPAN class="token function">DateAdd</SPAN><SPAN class="punctuation token">(</SPAN>$feature<SPAN class="punctuation token">.</SPAN>ReportDate<SPAN class="punctuation token">,</SPAN> <SPAN class="number token">1</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="string token">'days'</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">,</SPAN> dateFormat<SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">;</SPAN>
<SPAN class="keyword token">var</SPAN> dateFrom <SPAN class="operator token">=</SPAN> <SPAN class="token function">Text</SPAN><SPAN class="punctuation token">(</SPAN><SPAN class="token function">DateAdd</SPAN><SPAN class="punctuation token">(</SPAN>$feature<SPAN class="punctuation token">.</SPAN>ReportDate<SPAN class="punctuation token">,</SPAN> <SPAN class="operator token">-</SPAN><SPAN class="number token">6</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="string token">'days'</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">,</SPAN> dateFormat<SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">;</SPAN>
<SPAN class="token function">console</SPAN><SPAN class="punctuation token">(</SPAN><SPAN class="string token">"Report Date between "</SPAN> <SPAN class="operator token">+</SPAN> dateFrom <SPAN class="operator token">+</SPAN> <SPAN class="string token">' and date '</SPAN> <SPAN class="operator token">+</SPAN> dateTo <SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">;</SPAN>


<SPAN class="comment token">// define query and apply filter</SPAN>
<SPAN class="keyword token">var</SPAN> sql <SPAN class="operator token">=</SPAN> <SPAN class="string token">"ReportDate BETWEEN date '"</SPAN> <SPAN class="operator token">+</SPAN> dateFrom <SPAN class="operator token">+</SPAN> <SPAN class="string token">"' and date '"</SPAN> <SPAN class="operator token">+</SPAN> dateTo <SPAN class="operator token">+</SPAN> <SPAN class="string token">"'"</SPAN><SPAN class="punctuation token">;</SPAN>
<SPAN class="keyword token">var</SPAN> fs <SPAN class="operator token">=</SPAN> <SPAN class="token function">Filter</SPAN><SPAN class="punctuation token">(</SPAN>$layer<SPAN class="punctuation token">,</SPAN> sql<SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">;</SPAN> 

<SPAN class="comment token">// summarize values in field Positive for selected features</SPAN>
<SPAN class="keyword token">return</SPAN> <SPAN class="token function">Sum</SPAN><SPAN class="punctuation token">(</SPAN>fs<SPAN class="punctuation token">,</SPAN> <SPAN class="string token">"Positive"</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">;</SPAN><SPAN class="line-numbers-rows"><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN></SPAN>

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.

XanderBakker

Hi heathmanderson ,

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

<SPAN class="comment token">// define dates</SPAN>
<SPAN class="keyword token">var</SPAN> dateFormat <SPAN class="operator token">=</SPAN> <SPAN class="string token">"MM/DD/Y"</SPAN><SPAN class="punctuation token">;</SPAN> <SPAN class="comment token">// 12/31/2019</SPAN>
<SPAN class="keyword token">var</SPAN> dateTo <SPAN class="operator token">=</SPAN> <SPAN class="token function">Text</SPAN><SPAN class="punctuation token">(</SPAN><SPAN class="token function">Date</SPAN><SPAN class="punctuation token">(</SPAN>$feature<SPAN class="punctuation token">.</SPAN>ReportDate<SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">,</SPAN> dateFormat<SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">;</SPAN>
<SPAN class="keyword token">var</SPAN> dateFrom <SPAN class="operator token">=</SPAN> <SPAN class="token function">Text</SPAN><SPAN class="punctuation token">(</SPAN><SPAN class="token function">DateAdd</SPAN><SPAN class="punctuation token">(</SPAN>$feature<SPAN class="punctuation token">.</SPAN>ReportDate<SPAN class="punctuation token">,</SPAN> <SPAN class="operator token">-</SPAN><SPAN class="number token">7</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="string token">'days'</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">,</SPAN> dateFormat<SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">;</SPAN>

<SPAN class="comment token">// define query and apply filter</SPAN>
<SPAN class="keyword token">var</SPAN> sql <SPAN class="operator token">=</SPAN> <SPAN class="string token">"ReportDate BETWEEN date '"</SPAN> <SPAN class="operator token">+</SPAN> dateFrom <SPAN class="operator token">+</SPAN> <SPAN class="string token">"' and date '"</SPAN> <SPAN class="operator token">+</SPAN> dateTo <SPAN class="operator token">+</SPAN> <SPAN class="string token">"'"</SPAN><SPAN class="punctuation token">;</SPAN>
<SPAN class="keyword token">var</SPAN> fs <SPAN class="operator token">=</SPAN> <SPAN class="token function">Filter</SPAN><SPAN class="punctuation token">(</SPAN>$layer<SPAN class="punctuation token">,</SPAN> sql<SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">;</SPAN> 

<SPAN class="comment token">// summarize values in field Active for selected features</SPAN>
<SPAN class="keyword token">return</SPAN> <SPAN class="token function">Sum</SPAN><SPAN class="punctuation token">(</SPAN>fs<SPAN class="punctuation token">,</SPAN> <SPAN class="string token">"Active"</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">;</SPAN>
‍‍‍‍‍‍‍‍‍‍‍<SPAN class="line-numbers-rows"><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN></SPAN>

Nimm an der Unterhaltung teil

Angemeldete Mitglieder können Beiträge verfassen, Updates folgen und mehr. Neu hier? Registriere ein kostenloses Konto.

Schnellzugriffe

Community Help
Find useful guides, FAQs, and documents to help you navigate and make the most of Esri Community.