Aggregate Multiple Yes No Fields into Pie Chart

[Caitlin_Todd_LCOR]

Hi all, 

I'm likely overthinking this. I have a hosted feature layer of crash incidents that contains multiple Yes/No fields to record what was involved that could've led to the crash. I have a request to make a dashboard of this information using a Pie Chart to show a count of each "effect" involved in crash. 

But, in some cases there are many "effects" with a Yes. An example of what the table looks like is below.

alcohol	animal	fixed object	motorcycle
N	N	Y	N
Y	Y	N	Y
Y	N	N	Y

My end result would be a cumulative tally of "Y" for each category of effects.

So the pie chart category would be the field name and the value would be a count of "Y". Something like:

Category	Count
alcohol	15
animal	25
motorcycle	11

So where a crash involved multiple "effects" each "Y" would increase the count for their respective "effect" category.

My expression so far looks like:

// set portal variable and the feature set 
var p = Portal('portalURL');
var fs = FeatureSetByPortalItem(p,'ItemID',19,['OBJECTID','alc_inv','drug_inv','marij_inv','off_road','head_on','fixed_obj','animal'],false);

//filter fs to where any of effect is "Y"
var sql = `alc_inv = 'Y'OR drug_inv = 'Y'OR marij_inv='Y' OR off_road='Y'OR head_on = 'Y' OR fixed_obj='Y' OR animal='Y'`;
var flt = Filter(fs, sql);

// define the output dict
var out_fs = {
    geometryType: "",
    fields: [
        {name: "count", type: "esriFieldTypeInteger"},
        {name: "category", type:"esriFieldTypeString"}
    ],
    features: []
}
var index = 0; 

// Loop and store attributes 
for (var f in fs) { 
  //get the field names from the filtered schema
  var schary = Schema(flt);
  var ary = schary['fields'];

  out_fs.features[index] = { 
   'attributes': {  
      'count': f['OBJECTID'], 
      'category':ary['name']
    }
  }
  for (var j in ary) {
      if([ary[j]['name']]=='Y' && ary[j]['name'] !='OBJECTID_1' ||ary[j]['name'] !='OBJECTID'){
        Push(out_fs.category,ary[j]['name'])}}
   
  index++;}
var fs_dict = FeatureSet(Text(out_fs));
return fs_dict

I haven't been lucky enough to find other questions with this particular issue, but there has to be a better way to write this. 

I greatly appreciate any and all help!

Caitlin T 

Lane County, OR GIS

[jcarlson · Accepted solution]

It looks like things are getting a little mixed up in the push functions. You're trying to push something into out_fs.category, but your out_fs doesn't actually have that as an array you can push into.

All you need is a featureset with "type", and the pie chart can aggregate everything for you. You are on the right track just iterating through the attributes, but when you have a feature, you can do that by using for (var attr in your_feature). And if we only ask for the fields we care about, you can just have a simple check for 'Y' and push that. Try this:

// set portal variable and the feature set 
var p = Portal('portalURL');
var fs = FeatureSetByPortalItem(
  p,
  'ItemID',
  19,
  ['alc_inv','drug_inv','marij_inv','off_road','head_on','fixed_obj','animal'],
  false
);

//filter fs to where any of effect is "Y"
var sql = `alc_inv = 'Y' OR drug_inv = 'Y' OR marij_inv='Y' OR off_road='Y' OR head_on = 'Y' OR fixed_obj='Y' OR animal='Y'`;
var flt = Filter(fs, sql);

// define the output dict
var out_fs = {
    geometryType: "",
    fields: [{name: "category", type:"esriFieldTypeString"}],
    features: []
}

// Loop and store attributes 
for (var f in fs) { 
  // iterate over fields
  for (var att in f){
    if (f[att] == 'Y') {
      Push(
        out_fs['features'],
        { attributes: { category: att } }
      )
    }
  }
}

var fs_dict = FeatureSet(Text(out_fs));
return fs_dict

 When you get the output, just tell the pie chart to do Grouped Values based on the category field, and use the statistic Count.

[Caitlin_Todd_LCOR]

Hi Josh!

Thank you for helping me out with this. My brain is trying to do gymnastics and I'm definitely overthinking.  I compared the final count from the expression with a series of Select by Attribute in ArcGIS Pro on each field to get a true count.

While I do get a result with the expression you provided, the final count of each category is off by a few. Each final count is less than the true count I got from Pro by different amounts.

I double-checked the data to ensure there's no hidden spaces or typos anywhere in the original hosted feature layer so it's not because of that.. 

Does this section of the expression push out every attribute that equals "Y" in a single row? For example, say Alcohol, Animal, and Fixed Object are "Y" in one row. Are three rows created in the out_fs or just one for the first "Y" it found?

// Loop and store attributes
for (var f in flt) {
  // iterate over fields
  for (var att in f){
    if (f[att] == 'Y') {
      Push(
        out_fs['features'],
        { attributes: { category: att } }
      )
    }
  }

Just trying to think through what the potential reasons could be for the final count to be less than the true count

Caitlin

[jcarlson]

Correct, if all three categories were 'Y', then three entries are made in the output. How much are the amounts off?

If you're trying to understand how and why an expression is behaving strangely, it's a good idea to add Console statements in your expression. Like Console('checking attributes for feature ', f) inside the "loop and store" section.

[EH_Alaska]

I'm more of a data management type than a programmer so most of my work-arounds go back to the source data. 😁  I would convert your table so that each column is broken down into individual records like so:

Alcohol	Y
Alcohol	N
Alcohol	N
Alcohol	Y
Animal	Y
Animal	Y
Animal	N

I imagine each of these have a unique identifier that wasn't included in your original post, so that would be the first column. Anyways, once this is done I'd filter to "Y" only. The pie chart will then show a count for all of your effects. Not the perfect solution, but I hope it helps someone!

[Caitlin_Todd_LCOR]

@jcarlson  the amount of difference between them varies. Some of the categories off by 6, and others are off by 12 or 5. There's a couple that are spot on, those had a count of 1 and 2.

I tested a couple things in case it was something I did wrong in Pro. I'll use the Alcohol field as an example.

• I looked at the data table of the hosted feature layer and clicked on the detailed view of the Alcohol field. It shows a total of 48 "Y"s. But the Pie Chart shows 42.
• I tested swapping out the pie chart data source to the hosted feature layer without any Arcade expressions and grouped by the Alcohol field. It also resulted in 48 counts of "Y". 
• I added some Console statements to check on the counting and it seems to be counting correctly. 

I have the pie chart element configured simply like this for testing.

So that leaves me pretty stumped on how to troubleshoot further.

If its not the data, nor the Arcade expression, nor the Dashboard Pie Chart element then I'm not sure what else there is to check. 

I may have to get creative with a workaround to meet my user's request for now, and then revisit when I have time. 

 @EH_Alaska Nice idea! Unfortunately the data I have to work with wasn't managed super well and there is no unique ID. There's a field for it, but its entirely null.

[jcarlson]

Well, we could take a different approach: GroupBy. We can create calculated fields directly in the function and avoid a loop entirely.

// set portal variable and the feature set 
var p = Portal('portalURL');
var fs = FeatureSetByPortalItem(p,'ItemID',19,['alc_inv','drug_inv','marij_inv','off_road','head_on','fixed_obj','animal'],false);

// filter fs to where any of effect is "Y"
var sql = `alc_inv = 'Y' OR drug_inv = 'Y' OR marij_inv='Y' OR off_road='Y' OR head_on = 'Y' OR fixed_obj='Y' OR animal='Y'`;
var flt = Filter(fs, sql);

// group the filtered featureset, sum "Y" values
var counts = GroupBy(
  flt,
  { name: 'placeholderID', expression: 'placeholderID' },
  [
    { name: 'alc_inv',   expression: "CASE WHEN alc_inv = 'Y' THEN 1 ELSE 0 END",   statistic: 'SUM' },
    { name: 'drug_inv',  expression: "CASE WHEN drug_inv = 'Y' THEN 1 ELSE 0 END",  statistic: 'SUM' },
    { name: 'marij_inv', expression: "CASE WHEN marij_inv = 'Y' THEN 1 ELSE 0 END", statistic: 'SUM' },
    { name: 'off_road',  expression: "CASE WHEN off_road = 'Y' THEN 1 ELSE 0 END",  statistic: 'SUM' },
    { name: 'head_on',   expression: "CASE WHEN head_on = 'Y' THEN 1 ELSE 0 END",   statistic: 'SUM' },
    { name: 'fixed_obj', expression: "CASE WHEN fixed_obj = 'Y' THEN 1 ELSE 0 END", statistic: 'SUM' },
    { name: 'animal',    expression: "CASE WHEN animal = 'Y' THEN 1 ELSE 0 END",    statistic: 'SUM' }
  ]
)

return counts

This will be asking the server itself to do the counting. Since the output will be a single row, you can set the pie chart to Feature and then add the different fields in. If the counts are off on this, take a closer look at the filter you're applying. Maybe try omitting the filter and running this straight off of fs. Since GroupBy sends a single query to the server, it should be pretty fast with or without a filter in place.

[Caitlin_Todd_LCOR]

Hi all, 

I was pulled away for wildfires in our area and had to put this down for a little while. After revisiting this and getting more eyes on it we discovered why the numbers weren't lining up! It wasn't the expression, that is working perfectly and I marked Josh's solution as the answer for this post. 

The "wrench in the cog" was a service overwrite that happened after the expression was put in place, both the item ID and the layer order was changed. So instead of being "Item ID Some Number (19)", it became "ItemID Some other number (2)" After updating the expression the correct numbers are now showing.

@jcarlson Thanks so much for your patience and your help with this!! I was so buried in the weeds I couldn't see the answer right in front of my nose.

Caitlin

Lane Co. GIS