topic Re: Help with Data Expression for Table Widget in ArcGIS Dashboard in ArcGIS Dashboards Questions

Help with Data Expression for Table Widget in ArcGIS Dashboard

GeoGeek — Thu, 05 Jun 2025 08:00:40 GMT

I'm seeking assistance with creating data expression for a table widget in ArcGIS Dashboard. I've been experimenting but haven't been able to achieve the desired outcome.

Here is my sample data:

Sample data

Only two fields are involved.

Here is the desired result:

Desired outcome in table widget

I've tried using Arcade expressions, but I'm unsure how to structure it.

Any guidance, examples, or resources would be greatly appreciated.

Re: Help with Data Expression for Table Widget in ArcGIS Dashboard

KenBuja — Thu, 05 Jun 2025 13:49:09 GMT

Here's one way to do that. I used a dummy dataset to test it out, so substitute your actual dataset.

var fs = FeatureSet( { fields: [ { alias: "Person", name: "Person", type: "esriFieldTypeString" }, { alias: "Status", name: "Status", type: "esriFieldTypeString" } ], features: [ { attributes: { Person: "Person 1", Status: "Investigated" } }, { attributes: { Person: "Person 1", Status: "Investigated" } }, { attributes: { Person: "Person 1", Status: "Pending" } }, { attributes: { Person: "Person 1", Status: "Investigated" } }, { attributes: { Person: "Person 2", Status: "Investigated" } }, { attributes: { Person: "Person 2", Status: "Pending" } }, { attributes: { Person: "Person 2", Status: "Pending" } }, { attributes: { Person: "Person 2", Status: "Pending" } }, { attributes: { Person: "Person 2", Status: "Investigated" } }, { attributes: { Person: "Person 2", Status: "Investigated" } }, { attributes: { Person: "Person 3", Status: "Investigated" } }, { attributes: { Person: "Person 3", Status: "Investigated" } }, { attributes: { Person: "Person 3", Status: "Pending" } } ] } ); var statuses = ["Investigated", "Pending"]; var people = GroupBy(fs, "Person", { name: "Total", expression: "1", statistic: "Count" }); var peopleandstats = GroupBy(fs, ["Person", "Status"], { name: "Total", expression: "1", statistic: "Count" }); var features = []; for (var p of people) { var person = p.Person; var attr = {}; attr["Person"] = person; for (var status of statuses) { var s = First(Filter(peopleandstats, "Person = @person AND Status = @status")); attr[status] = s.Total; if (status == "Investigated") attr["Percentage"] = Round(s.Total / p.Total * 100, 0); } attr["Total"] = p.Total; Push(features, { attributes: attr }); } var fields = [ { name: "Person", type: "esriFieldTypeString" }, { name: "Investigated", type: "esriFieldTypeInteger" }, { name: "Pending", type: "esriFieldTypeInteger" }, { name: "Total", type: "esriFieldTypeInteger" }, { name: "Percentage", type: "esriFieldTypeInteger" } ]; return FeatureSet({ fields: fields, features: features });

which returns this table

Re: Help with Data Expression for Table Widget in ArcGIS Dashboard

GeoGeek — Mon, 09 Jun 2025 06:49:17 GMT

Hi @KenBuja. Thank you for responding to my question.

Unfortunately, when I applied the expression to my dataset, it didn't work as expected. I suspect I might be misapplying part of the data expression.

Update

I made some progress, but I am stuck with getting the percentage.

var portal = Portal('https://arcgis.com/');

var fs = FeatureSetByPortalItem(

portal,

'9b5e49e6c3454c9fb6c1e0de31252e98',

[

'INVESTIGATED_BY',

'Status'

false

);

var investigated = "CASE WHEN Status = 'Investigated' THEN 1 ELSE 0 END"

var pending = "CASE WHEN Status = 'Pending' THEN 1 ELSE 0 END"

return Groupby(

fs,

['INVESTIGATED_BY'],

[

{name:'Total', expression: 'Status', statistic: 'COUNT'},

{name:'Investigated', expression: investigated, statistic: 'SUM'},

{name:'Pending', expression: pending, statistic: 'SUM'}

]

);

Re: Help with Data Expression for Table Widget in ArcGIS Dashboard

KenBuja — Mon, 09 Jun 2025 13:48:36 GMT

Good use of the Case statement in the GroupBy function. I always overlook that as a possible solution.

I believe you'll still have to loop through the grouped Featureset to calculate the percentage.