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

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.