topic Re: Cumulative Sum with Arcade in ArcGIS Dashboards Questions

Cumulative Sum with Arcade

TimWillson — Mon, 28 Oct 2024 14:31:53 GMT

Getting stuck on solving this dashboard output in creating a cumulative sum over each year. In this dataset, when I return GroupBy(fs, 'dc', {name: "Total Meters", expression: "total_gis_meters", statistic: "SUM"}), I get the following:

dc Total Meters ROW__ID

2020	211211	1
2021	2811	68005
2022	3123	7520
2023	3405	49435
2024	3346	259655

What I want to do now is creating a running total for each year. I assume I need a loop, but nothing I've tried is working. It shouldn't be this difficult, I wouldn't think.

Thank you for any suggestions!

Re: Cumulative Sum with Arcade

jcarlson — Tue, 29 Oct 2024 01:25:13 GMT

You can do a cumulative sum with the right SQL expressions. The most straightforward way is to use a loop with the distinct year values.

// get distinct year values var years = Distinct(fs, 'dc') // output featureset dict var out_dict = { fields: [ {name: 'dc', type: 'esriFieldTypeInteger'}, // assuming your year is an integer {name: 'year_sum', type: 'esriFieldTypeInteger'}, {name: 'cum_sum', type: 'esriFieldTypeInteger'} ], geometryType: '', features: [] } // loop over each year, getting the cumulative count and per-year count, add to output dict for (var y in years) { var yr = y['dc'] Push( out_dict['features'], { attributes: { dc: yr, year_sum: Sum(Filter(fs, `dc = ${yr}`), 'total_gis_meters'), cum_sum: Sum(Filter(fs, `dc <= ${yr}`), 'total_gis_meters') } ) } return FeatureSet(Text(out_dict))

Re: Cumulative Sum with Arcade

Matrio — Tue, 29 Oct 2024 05:49:52 GMT

To create a running total for each year, you can use a loop to accumulate totals. Here’s a quick example:

This will give you the cumulative sum for each year. If you encounter any issues, feel free to share.

Re: Cumulative Sum with Arcade

TimWillson — Tue, 29 Oct 2024 12:32:30 GMT

Perfect! Thank you! This has baffled me for a week now!