Data expression that takes two numeric fields and calculates percentage change

[JimMiller3]

I have a feature layer containing several years of school enrollment data by county. Among the fields are current year enrollment (curr_enr) and previous year enrollment (prev_enr).

Using those fields, I want to calculate percentage change and display that value in a dashboard serial chart. That is, ($feature.curr_enr-$.prev_enr)/$feature.prev_enr

Based on other posts, it seems that my plan -- a dashboard serial chart showing percentage change by year -- requires a data expression.

I've created a variable for my source feature layer. But I'm stuck on how to calculate percentage change from the two enrollment fields and returning that value to show on the serial chart. (Once created, the serial chart would be controlled by a county selector.) Thank you for any pointers.

[Neal_t_k]

Something like this should get you started.

var fs = FeatureSetByPortalItem(
    Portal("https://www.arcgis.com"),
    "<itemID>",
    <layerIndex>,
    ["County", "Year", "curr_enr", "prev_enr"],
    false
);

var features = [];

for (var f in fs) {
    var county = f["County"];
    var year = f["Year"];
    var curr = f["curr_enr"];
    var prev = f["prev_enr"];

    if (IsEmpty(curr) || IsEmpty(prev) || prev == 0) {
        continue;
    }

    var pctChange = ((curr - prev) / prev) * 100;

    Push(features, {
        attributes: {
            County: county,
            Year: year,
            PercentChange: pctChange
        }
    });
}

return FeatureSet(Text({
    fields: [
        { name: "County", type: "esriFieldTypeString" },
        { name: "Year", type: "esriFieldTypeInteger" },
        { name: "PercentChange", type: "esriFieldTypeDouble" }
    ],
    geometryType: "",
    features: features
}));

[KenBuja]

You can use the GroupBy function to calculate this

var fs = FeatureSetByPortalItem(
  Portal("https://www.arcgis.com"),
  "yourID",
  0,
  ['County','Year', 'curr_enr','prev_enr'],
 false
)
GroupBy(fs, ['County','Year','curr_enr','prev_enr'], {name: 'Change', expression: '(curr_enr - prev_enr)/prev_enr', statistic: 'Max'})

[Neal_t_k]

@KenBuja I need to learn groupby better, much shorter, and all at once.

[JimMiller3]

Thank you @Neal_t_k and @KenBuja for the above responses. Both suggestions work perfectly if I select any of California’s 58 counties.

Problems occur when the county selector is set to “None.” In that case, three serial charts update: 1) cumulative enrollment by year for all of California's counties; 2) cumulative enrollment change by year for all of California's counties and 3) cumulative enrollment percentage change by year for all of California's counties.

The percentage change values are incorrect.

For example, the image below shows 2023-24 to 2024-25 percentage change on the serial chart and in an indicator using the percentage change calculated value. The percentage change value in the indicator card - -0.54% -- is correct. The percentage change value in the serial chart – 2.97% -- is incorrect.

Below is how things appear in the serial chart setup. I suspect the problem stems from the Statistic selection. It currently shows “Maximum” but any option I choose – Average, Minimum, etc. -- yields incorrect values. Is there a way to show percentage change value as is, without applying a statistic type to it? Or is there another approach to take. Thanks.

[Neal_t_k]

@JimMiller3   Did you try "Sum" percent change? How do you have it calculated in the indicator? 

[JimMiller3]

@Neal_t_k If I change the Statistic selection to "Sum" for the serial chart, it displays -13.53%. That is incorrect. It is the sum of the 58 counties' respective percentage changes from 2023-24 to 2024-25, and not the cumulative percentage change for the state as a whole (which is the expected value.)

The indicator works as expected: It sums up curr_enr and prev_enr values in the Data section. In the Indicator, section, I choose "Percent Change" calculated values (images below.) I get the correct percentage change whether one county, 10 counties, or no county is selected.

So, in essence, what I want to do using a data expression or other approach is to show the same kind of calculation, by year, in a serial chart. 

    

[Neal_t_k]

@JimMiller3 I am not sure how to fix this, they are calculating different things.  Your chart is calculating the sum of the calculated year to year percent change.   Where as the indicator is adding up all the current years enrollment and then all the previous years enrollment and the calculating the percent change from those totals  e.g.

It appears the indicator is calculating correctly, while the serial table is only calculating correctly for the selected counties and only when a single year is selected...I don't know what the solution is for this, other than being selective on what and how you show the data.  Maybe don't show percent change in the serial chart and only show current enrollment, then set a chart action to filter the indicator on selection of one of the columns in the chart.