Help with Arcade expression (percentage change)

JoshNock1

Hi,

I am looking for some assistance with an arcade expression to use on ArcPro to find the percentage change of a dataset month by month.

I have provided an example dataset below.

Location	Grand Total	Date
London	2	Dec_2021
New York	7	Dec_2021
Paris	8	Dec_2021
London	3	Nov_2021
New York	7	Nov_2021
Paris	3	Nov_2021
London	1	Oct_2021
New York	4	Oct_2021
Paris	9	Oct_2021

 

I believe the result should be as below

Location	Grand Total	Date	Percentage change (%)
London	2	Dec_2021	-33.33
New York	7	Dec_2021	0
Paris	8	Dec_2021	167.67
London	3	Nov_2021	200
New York	7	Nov_2021	75
Paris	3	Nov_2021	-66.67
London	1	Oct_2021	n/a
New York	4	Oct_2021	n/a
Paris	9	Oct_2021	n/a

 

The general formula I used is below, but looking for a way to automate this as I have thousands of rows of data

((Nov_2021)-(Dec_2021))/(Nov_2021))*100

Many thanks

Hornbydd

The first step is to simplify your input otherwise you would need some complex logic to identify as you step through the table you have gone from Paris Dec_2021 to London Nov_2021.

If your input is truly structured as you have shown then you could very easily explode out this table using the Split by attributes tool.

This creates as many tables as you have locations, the output of one such table would be:

Depending upon your input structure you may need to sort the table into a correct date sequence.

Add a new field of type double to record your change then run the following python code in a field calculate tool

 

Code block is:

l = list() # remember lists start at index zero
with arcpy.da.SearchCursor("London","GrandTotal") as cur:
    for row in cur:
        l.append(row[0])
        
def offset(i,currentTotal):
    global l
    if i < len(l):
        prev_month = l[i]
        calc = ((currentTotal - prev_month) / prev_month)*100
        return calc

 

In the example above I have hardwired it to read the table "London" but you could easily swap that out with an inline variable substitution from a model iterator as suggested by @Robert_LeClair