Help with Data Expression for Table Widget in ArcGIS Dashboard

[GeoGeek]

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. 

[KenBuja]

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

 

[GeoGeek]

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',

    0,

    [

        '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'}

]

);

[KenBuja]

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. 

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

var grouped = Groupby(
  fs,
  ["Person"],
  [
    { name: "Total", expression: "Status", statistic: "COUNT" },
    { name: "Investigated", expression: investigated, statistic: "SUM" },
    { name: "Pending", expression: pending, statistic: "SUM" }
  ]
);

var fields = Schema(grouped).fields;
Push(fields, { name: "Percentage", type: "esriFieldTypeDouble" });
var features = [];
for (var g in grouped) {
  push(
    features,
    {
      attributes:
        {
          Total: g.Total,
          Investigated: g.Investigated,
          Pending: g.Pending,
          Percentage: Round(g.Investigated / g.Total * 100)
        }
    }
  );
}
return FeatureSet({ fields: fields, features: features });