Select to view content in your preferred language


05-18-2023 06:08 PM
New Contributor II

Hi guys,

I have a table that I want to use the groupby function in 2 different ways, then join it back to calculate the percentages to display on ArcGIS dashboard. I am very new to ArcGIS Arcade so this has been a major struggle. Can someone please provide some guidance? 

What I have got so far:

//Grab data
var portal = Portal('');
var fs = FeatureSetByPortalItem(portal,'id',0,['LGA','Activity']);
//Table  1
var LGA_Activity_Count = GroupBy(fs,['LGA','Activity'],[
//Table 2 
var LGA_Activity_Count = GroupBy(fs,['Activity'],[
//Join Table 


//(Table 1 divide by table 2) * 100 to work out the percentages

0 Kudos
4 Replies
MVP Frequent Contributor


// function to load Featureset into RAM (for performance)
function Memorize(fs) {var temp_dict = {fields: Schema(fs)['fields'], geometryType: '', features: []}; for (var f in fs) {var attrs = {}; for (var attr in f) {attrs[attr] = Iif(TypeOf(f[attr]) == 'Date', Number(f[attr]), f[attr]);} Push(temp_dict['features'], {attributes: attrs});} return FeatureSet(Text(temp_dict))}

// load Featureset
var fs = Memorize(FeaturesetByPortalItem(Portal(""), "b0d335151aad48a5883326b9aed69cdd", 0, ["LGA", "Activity"], false))

// GroupBy county and activity fields
var local_activities = GroupBy(fs, ["LGA", "Activity"], [{name: "LocalActivityCount", expression: "1", statistic: "COUNT"}])
// GroupBy activity field
var state_activities = GroupBy(fs, ["Activity"], [{name: "StateActivityCount", expression: "1", statistic: "COUNT"}])

// create empty output featureset ( add percentage field to local_activities)
var out_fs = {
  geometryType: "",
  fields: Splice(
            [{name:"PercentageOfTotal", type: "esriFieldTypeDouble"}]
  features: []

// iterate through the local activities
for(var f_local in local_activities) {
  // find the corresponding feature in the state activities
  var a = f_local.Activity
  var f_state = First(Filter(state_activities, "Activity = @a"))
  // copy the local activity attributes
  var att = Dictionary(Text(f_local)).attributes
  // calculate and append the percentage
  att.PercentageOfTotal = f_local.LocalActivityCount / f_state.StateActivityCount * 100
  // add the new feature to the output fs
  Push(out_fs.features, {attributes: att})

return Featureset(Text(out_fs))


Have a great day!
New Contributor II

Hi Johannes, 

Thank you so much for taking the time to answer my question!

Unfortunately I've received an error message (image below). I am thinking this might have something to do with the "Activity" column that's sitting in the back-end as there are few records showing null/blank value. Is there any way to fix this? 




0 Kudos
MVP Frequent Contributor

Yes, it is because of null values. To fix it, you can insert this after line 7 to ignore features with null values in either field:

fs = Filter(fs, "LGA IS NOT NULL AND Activity IS NOT NULL")

Have a great day!
New Contributor II

Thank you so much Johannes. This is exactly what I am chasing after :). 

Any chance you know why the "StateActivityCount" column isn't displaying? 




0 Kudos