Data Expression: Combined Category Count for Multiple Fields with Same Domain

[SamSlater]

I have points that are given a primary category field and secondary category field of the same domain. I am attempting to use a pie chart to display the total count of points per category from both fields. I am trying to create a data expression to sum the count of categories from both fields:

'Comment_Type'

'Secondary_Type'

var p = 'https://xyzxyzxy.maps.arcgis.com'

var itemId = 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx'

var fs = FeatureSetByPortalItem(Portal(p), itemId, 0, ['*'], false)

var comment_stats = GroupBy(fs, ['Comment_Type'],

                [ { name: 'Comment_Type',

                    expression: 'Comment_Type',

                  statistic: 'COUNT' },

                    { name: 'Secondary_Type',

                   expression: 'Secondary_Type',

                    statistic: 'COUNT' },

                 ])

How do I get the table to use the domain label for the first column?

Why does the Secondary_Type column not match its actual values?

Is there a more efficient way of doing this?

var comment_totals = GroupBy(comment_stats, ['Comment_Type'],

                   [ { name: 'Totals',

                   expression: 'Comment_Type + Secondary_Type',

                   statistic: 'SUM' },

                    ])

return comment_totals

[KenBuja · Accepted solution]

I don't think that's possible with single GroupBy, but I'd love to be proved wrong.

You'll have to do two GroupBys, one for each field. That will give you the count for each of the fields. Then you can combine them into a single table with the counts of both and their sums

Unfortunately, the GroupBy does strip the domain information from your result. But you can add that back in when combining the tables by looping through all of the domain codes. This code loops through all the domain codes (and this assumes you're using the same domain for both fields) and filters the grouped tables to get their counts.

var fs = FeatureSetByPortalItem(Portal("your portal"), itemID, 0, ["*"], false);
var primaryField = "Comment_Type";
var primary = GroupBy(
  fs,
  [primaryField],
  [{ name: "Primary Count", expression: primaryField, statistic: "Count" }]
);
var secondaryField = "Secondary_Type";
var secondary = GroupBy(
  fs,
  [secondaryField],
  [{ name: "Secondary Count", expression: secondaryField, statistic: "Count" }]
);

var theDomain = Domain(fs, primaryField).codedValues;
//return theDomain;

var features = [];

for (var dom of theDomain) {
  var code = dom.code;

  var filterPrime = Filter(primary, "Comment_Type = @code");
  var filterSecondary = Filter(secondary, "Secondary_Type = @code");
  var primaryCount = iif(
    Count(filterPrime) > 0,
    First(filterPrime)["Primary Count"],
    0
  );
  var secondaryCount = iif(
    Count(filterSecondary) > 0,
    First(filterSecondary)["Secondary Count"],
    0
  );
  Push(
    features,
    {
      attributes:
        {
          Comment: dom.name,
          Primary: primaryCount,
          Secondary: secondaryCount,
          Total: primaryCount + secondaryCount
        }
    }
  );
}

FeatureSet(
  {
    fields: [
      { name: "Comment", type: "esriFieldTypeString" },
      { name: "Primary", type: "esriFieldTypeInteger" },
      { name: "Secondary", type: "esriFieldTypeInteger" },
      { name: "Total", type: "esriFieldTypeInteger" }
    ],
    features: features
  }
);

 This code worked properly for my test data and I hopefully didn't get anything wrong when substituting in your fields.

[SamuelSlaterPNW]

This is excellent! KenBuja I see why you have that "MVP" tag next to your name!

Is there a particular resource you would point newbies to to learn this level of arcade?

[KenBuja]

If this did answer your question, don't forget to click the "Accept as Solution" button. That will help others in research similar questions. And help me maintain my MVP status 🙂

The best place to start would be the Home page of the Arcade documentation, focusing on the Language features section. I would also take a look at some of the Arcade blogs that Esri has written, such with this one, which focuses on scripting for popup. I would also look at some of the posts that @jcarlson has written, since he has scripted some pretty nifty solutions to various problems.