How to create cumulative field calculation in Arcade?

Question

I have a simple table which records dates, types and locations of events. How would I create a field or view in which the cumulative sum of events by date would be recorded as is diplayed below?


FID,Date,Type,Location,Total,Sum_typ,sum_loc0,19.03.2020,confirmed,Hamburg,8,5,32,19.03.2020,confirmed,Hannover,8,5,33,21.03.2020,not confirmed,Hamburg,8,3,34,21.03.2020,not confirmed,Hannover,8,3,35,21.03.2020,confirmed,Bremen,8,5,26,24.03.2020,not confirmed,Hannover,8,3,37,25.03.2020,confirmed,Bremen,8,5,28,25.03.2020,confirmed,Hamburg,8,5,3

PeterMilenkovic · Answer

Digging up this thread because I calculated a cumulative total in a data expression for a dashboard.

// Featureset to use

var B = FeatureSetByPortalItem(Portal('https://cityofsydney.maps.arcgis.com'),'4298a848945f43868ba744033ceabc2e',0,['SurveyableSpaces','AssignmentStatus','completeddate'])

// Simple filter
var FB = Filter(B, 'AssignmentStatus = 3')

// First step was to create an empty featureset, i did this as i had to steralise the completeddate field to remove hours/mins/sec (group by later)

var FBSD = {
  fields: [{'name':'Date', 'type':'esriFieldTypeDate'},
             {'name':'Spaces', 'type':'esriFieldTypeDouble'}
            ],
  'geometryType': '',
  'features': []
            }
// calculating dates in featureset require UNIX timestamps
var start = ToLocal(Date(1970, 0, 01, 0, 0, 0, 0))

// Looping though the filtered featureset, create new features and append to empty featureset

for(var f in FB) {
  var new_f = {'attributes': {'Date':DateDiff(
                                                Date(Year(f['completeddate']),
                                                Month(f['completeddate']),
                                                Day(f['completeddate']),
                                                0,
                                                0,
                                                0,
                                                0)
                                                ,start, 'milliseconds'),
                              'Spaces': f['SurveyableSpaces']}

}
Push(FBSD.features, new_f)
                    }
// calculate group by
var GB = GroupBy(Featureset(Text(FBSD)),'Date',[{name: 'DailyTotal', expression:'Spaces',statistic:'SUM'}])

// create empty featureset to store final features
var RT = {
  fields: [{'name':'Date', 'type':'esriFieldTypeDate'},
             {'name':'DailyTotal', 'type':'esriFieldTypeDouble'},
             {'name':'RunningTotal', 'type':'esriFieldTypeDouble'}
            ],
  'geometryType': '',
  'features': []
            }
// create variable to store running total
var run = 0

// loop through group by featureset and "+=" on running total variable to append current daily total to previous running total in loop 
for(var f in GB) {
  run += f['DailyTotal'] 
  var new_f = {'attributes': {'Date':DateDiff(
                                                Date(Year(f['Date']),
                                                Month(f['Date']),
                                                Day(f['Date']),
                                                0,
                                                0,
                                                0,
                                                0)
                                                ,start, 'milliseconds'),
                              'DailyTotal': f['DailyTotal'],
                            'RunningTotal': run
                            }
              }
Push(RT.features, new_f)
                    }
return FeatureSet(Text(RT))

StacyCecil · Answer

I know this is a little old, but I am having the same issue. I am still horrible at coding, so I appreciate your patience while I learn things. I'm a biologist and coding is more of a tool than my bread an butter, so I may have some jargon messed up.

The example solution above will achieve the sum of the total field input into every record of the table. However, this is not quite what either OP or I am looking to do.

Instead, we likely need to script an iterator that moves through each record and sums the preceding value to the next record.

example:

IDAcresRunningAcreTotal11010235453651472123527150

My webmap has the acres calculated. What I need for various parties, is the cumulative sum field, or a running total of acres. Since we also want to try to keep this in AGOL and not move it back and forth between ArcGIS Pro, it would be nice if we had an arcade function to calculate this.

In python, I can easily achieve this with a helper function:

expression: AccumulateValue(!Acres!)

code block:

total = 0
def AccumulateValue(arg):
value = arg
    global total
    if total:
       total += value
    else: total = value
    return total

I have attempted to make this into an arcade function, but seem to be missing something since it only produces "" in my target field.

Arcade attempt:

var total = 0;
function AccumulateValue(Acres){
   var value = $feature.Acres;
    $total
    if (total){
    return total+=value
    }
    else {
    return total=value
    }
}

Is there a break where/how I am calling the function? Is there a problem with how I am defining the "value" variable? Is the way I've identified the "total" variable as a global the correct process?

I wasn't really sure how to adapt the expression and code block I use in ArcGIS Pro to the AGOL scripting box so those are the areas where I think the script is particularly causing problems.

BenTurrell · Answer

Hey Robert Buckley‌,

You can calculate the sum of all your layers like so:

var sum_values = SUM($layer,"Total")
return(sum_values)

Thanks,

Ben

If this answer was helpful please mark it as helpful. If this answer solved your question please mark it as the answer to help others who have the same question.