# How to create cumulative field calculation in Arcade?

1196
3
04-01-2020 07:16 AM
Occasional Contributor

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_loc
0,19.03.2020,confirmed,Hamburg,8,5,3
2,19.03.2020,confirmed,Hannover,8,5,3
3,21.03.2020,not confirmed,Hamburg,8,3,3
4,21.03.2020,not confirmed,Hannover,8,3,3
5,21.03.2020,confirmed,Bremen,8,5,2
6,24.03.2020,not confirmed,Hannover,8,3,3
7,25.03.2020,confirmed,Bremen,8,5,2
8,25.03.2020,confirmed,Hamburg,8,5,3``````
Tags (1)
3 Replies
Occasional Contributor III

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

New Contributor II

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:

 ID Acres RunningAcreTotal 1 10 10 2 35 45 3 6 51 4 72 123 5 27 150

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

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

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

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.

Occasional Contributor

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