FeatureSetByPortalName with subtotals by year

AmyRoust

I'm having trouble conceptualizing what I need to write in an Arcade statement. Here is a fake table that mirrors what I'm working with:

Year	Program_Type	Fed Fund 1	Fed Fund 2	Fed Fund 3	Local Fund 1	Local Fund 2	Local Fund 3	Local Fund 4
2015	Program Blue	0	0	0	5000	100	0	0
2015	Program Red	10000	9000	1000	1000	0	0	0
2016	Program Green	0	17000	0	3000	0	0	0
2016	Program Purple	0	0	5500	5500	5500	0	1700
2017	Program Orange	6500	500	0	1200	800	0	0

 

I need to return a table that looks like this:

Year	Fed Funding	Local Funding
2015	20000	100
2016	22500	7200
2017	7000	2000

 

The goal is to have a pie chart in a dashboard that compares combined Federal Funding to combined Local Funding. The default view will be all years combined, but I need the pie chart to update when the dashboard filter is set to a specific year.

Bonus points if I can figure out how to add a definition query to the table's results that allows me to filter out specific program types (e.g. add everything up as long as it's not from Program Orange). I can handle that part by creating a view layer from my hosted table, but if I can build it into the query, so much the better. Fewer items to manage in AGO.

I've gotten far enough that all of the values are added up, but I cannot get my mind wrapped around adding in the Year option.

// Fetches features from a public portal item
var fs = FeatureSetByPortalItem(
  Portal("https://xyz.maps.arcgis.com/"),
  "xyz",
  0,
  ["Program_Type","Fed Fund 1", "Fed Fund 2", "Fed Fund 3", "Local Fund 1", "Local Fund 2", "Local Fund 3", "Local Fund 4"],
  false
);

var fedtotal = Sum(fs, "Fed Fund 1") + Sum(fs, "Fed Fund 2") + Sum(fs, "Fed Fund 3");
var localtotal = Sum(fs, "Local Fund 1") + Sum(fs, "Local Fund 2") + Sum(fs, "Local Fund 3") + Sum("Local Fund 4");
var total = fedtotal + localtotal

var fed = round(when(fedtotal=='NaN', 0, fedtotal),2)
var local = round(when(localtotal=='NaN',0, localtotal),2)

return FeatureSet(
  {
    fields: [
      { name: "Federal", type: "esriFieldTypeSingle" },
      { name: "Local", type: "esriFieldTypeSingle" },
      { name: "Total", type: "esriFieldTypeSingle" }
    ],
    features: [{
      attributes: {Federal: fed, Local: local, Total: total}
    }]
  }
);

 

 

DavidSolari

A GroupBy call seems ideal. Here's an untested example:

// Fetches features from a public portal item
var fs = FeatureSetByPortalItem(
  Portal("https://xyz.maps.arcgis.com/"),
  "xyz",
  0,
  ["Year", "Fed_Fund_1", "Fed_Fund_2", "Fed_Fund_3", "Local_Fund_1", "Local_Fund_2", "Local_Fund_3", "Local_Fund_4"],
  false
);

var grouped = GroupBy(fs, "Year", [
    {
        name: "Fed_Funding",
        expression: "Fed_Fund_1 + Fed_Fund_2 + Fed_Fund_3",
        statistic: "SUM"
    }, {
        name: "Local_Funding",
        expression: "Local_Fund_1 + Local_Fund_2 + Local_Fund_3 + Local_Fund_4",
        statistic: "SUM"
    }
]);

return grouped