Cumulative Sum with Arcade

[TimWillson]

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!

[jcarlson · Accepted solution]

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

[TimWillson]

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

[Matrio]

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

let runningTotal = 0;
const results = GroupBy(fs, 'dc', {name: "Total Meters", expression: "total_gis_meters", statistic: "SUM"});

results.forEach(row => {
    runningTotal += row["Total Meters"];
    console.log(`Year: ${row.dc}, Running Total: ${runningTotal}`);
});

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