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 202021121112021281168005202231237520202334054943520243346259655 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!

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

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!

Find more posts tagged with

Accepted answers

jcarlson

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

All comments

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.

jcarlson

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

Join the Conversation

Signed in members can post, follow updates, and more. New here? Register a free account.

Quick Links

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