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 · Accepted solution]

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

[KenBuja]

And for the bonus, you can add a filter

// Fetches features from a public portal item
var fs = FeatureSetByPortalItem(
  Portal("https://xyz.maps.arcgis.com/"),
  "xyz",
  0,
  ["Year", "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 filtered = Filter(fs, "Program_Type <> 'Program Orange'")
return GroupBy(filtered, "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"
    }
]);

[AmyRoust]

@KenBuja , I should have just commented on the other post - recognize your code?

@DavidSolari - I had no idea that Arcade had a group by function! That's fantastic! Thank you.

[AmyRoust]

@KenBuja can I get your help one more time? Here is my current result table:

I need two more columns: one that divides the Fed_Funding category by the Total_Investment category and one that divides the Local_Funding category by the Total_Investment category. 

[KenBuja · Accepted solution]

If you had one entry per year in your data, you could have used the same GroupBy to create the two new columns dividing the sum of the federal (or local) investments by the sum of all investments. However, that won't work with multiple rows per year.

Instead you take the grouped FeatureSet and run another GroupBy to calculate those numbers.

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"
    }
  ]
);

GroupBy(grouped, "Year",
  [
    { name: "Federal Funding", expression: "Fed_Funding", statistic: "Max" },
    { name: "Local Funding", expression: "Local_Funding", statistic: "Max" },
    {
      name: "Total Investment",
      expression: "Fed_Funding + Local_Funding",
      statistic: "Max"
    },
    {
      name: "Federal Percentage",
      expression: "Fed_Funding/(Fed_Funding + Local_Funding)",
      statistic: "Max"
    },
    {
      name: "Local Percentage",
      expression: "Local_Funding/(Fed_Funding + Local_Funding)",
      statistic: "Max"
    }
  ]
);

[AmyRoust]

Thank you so much! If I had your address, I'd send you a fruit basket or an arrangement of some form. I appreciate you taking time out of your schedule to help strangers in Esriland.

[KenBuja]

Glad to help. And if it did solve your question, you can accept this as a solution in addition to David's reply