Select to view content in your preferred language

How to create cumulative field calculation in Arcade?

1919
3
04-01-2020 07:16 AM
RobertBuckley1
Frequent 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
BenTurrell
Frequent Contributor

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.

StacyCecil_
Emerging Contributor

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:

IDAcresRunningAcreTotal
11010
23545
3651
472123
527150

 

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.

0 Kudos
PeterMilenkovic
Frequent 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))

 

 

0 Kudos