How to Use Arcade GroupBy with Top and OrderBy Expressions

[izk_1989]

Hello,

I am struggling with using the Arcade's GroupBy with the Top and OrderBy Functions.

Basically I have a polygon layer which references and counts the number of features from another point layer, and I want to display in the popup the top 3 attributes based on counts within each polygon.

I have attached a file the arcade expression code I have so far for the polygon layer, however it is not working at moment (nothing being written in the popup). I am trying to get it to count the total number of point features within any given polygon, group by the attribute value of the facility_name field and find the top 3 facilities that have the highest point count

I am looking for the popup to show something like the following when a polygon is selected:

The Top 3 Facilities in this area are:

1. Facility A: 400 Patients

2. Facility B: 200 Patients

3. Facility C: 100 Patients

Greatly appreciate any help or advice with the code.

Regards,

Isaac

[KenBuja]

One thing I noticed is you used the line

var topFacilities = Top(OrderBy(Filter(stats, "facility_name <> ''"),"ClinicCount desc"), 3)

but later loop through a variable you haven't declared

for(var item in topClinics){

[XanderBakker · Accepted solution]

Hi @izk_1989 ,

In addition to what Ken correctly pointed out, there is another error: you don't have a total field, you have ClinicCount. I also formatted the result a bit differently.

// Reference the points feature layer
var patientsDA = FeatureSetByName($map,"PointsLayer")

// Count the number of patients within each polygon
var cntPatients = Intersects(patientsDA, $feature)

// Group patients by their facility and count
var stats = GroupBy(cntPatients, "facility_name", [
    {name: "ClinicCount", expression: "facility_name", statistic: "COUNT"}
    ])

//  Order the results in descending order by the total counts  
var topFacilities = Top(OrderBy(Filter(stats, "facility_name <> ''"),"ClinicCount desc"), 3)

// Popup placeholder
var result = 'The Top Facilities in this area are:'

// Write the results to the popup
if(Count(topFacilities) == 0){
    return "No Patients in this area"
}

var num = 0
// Format the results for display in popup
for(var item in topFacilities){
    num++
    var num_clinics = item["ClinicCount"]
    var clinic_name = item["facility_name"]
    
    result += TextFormatting.NewLine + num + ". " + clinic_name + ": " + num_clinics + " Patients"
    
}

return result

[izk_1989]

Thanks Xander, this solution works!

[MGT8C2U]

Is there a way to format the result variable text to show "The Top Facilites in this area are:" with an underline?  Or can you format the entire result with HTML somehow?

[VeronicaSarver]

Hi Xander.

Is there any way to do this same type of thing with line features? I have a feature layer that is "Paint Lines 2017". This layer contains 2 fields "PAINT_WID' and 'PAINT_COL'. I have been trying to modify your code above to fit a solution for what I need. I need to know the quantity of paint lines inside of a polygon based on their size ('PAINT_WID') and their color ('PAINT_COL') and I am trying to return the quantities of each  type in a popup in Arcgis Online. I attached a snip of what I am trying to achieve. I appreciate your help!

[izk_1989]

Thank you Ken, yes that is an error the topClinics should be using the topFacilities field

[Anonymous User]

Hello,

I found this thread while searching for help with using Orderby, however, I'm very new to Arcade and I am not sure where to insert the function. Here is my code that I created already.

var intersectLayer =Intersects(FeatureSetByName($map, "CAMS Address Points"), $feature)
var feature = FeatureSetByName($map, "CAMS Address Points")
var results = ""
for (var f in intersectLayer){
if (results ==""){
results = f.Number;
} else{
results += ", " + f.Number + iif(f.NumSuffix == '<Null>', ' ', f.NumSuffix);
}
}
return results

Basically, I have a parcel and I am trying to list the address points within that parcel.

Thank you in advance.