How to create Pivot Table like MS Excel in ArcGIS Dashboard

HunarSaleem

I have big Excel sheet table, contains province (16 provinces A, B, C, D.....etc) and a column name Status. The Status has many categories like ( Canceled, Completed, Extended, Initiated, Issued, Ongoing, Suspended ).

In the ArcGIS dashboard I need to create a table like below:

Province	Canceled	Completed	Extended	Initiated	Issued	Ongoing	Suspended	Grand Total
A	66	139	55	0	183	11	3	457
B	5	11	1	0	20	2	1	40
C	13	25	0	0	7	0	0	45
D	36	529	107	3	1	632	0	1,308
E	13	37	0	0	39	0	0	89
F	22	55	11	0	88	1	3	180
G	0	10	0	0	12	0	0	22
H	3	130	6	0	0	154	0	293
I	2	57	10	0	0	66	0	135
J	6	11	0	0	16	0	0	33
K	142	331	51	4	331	18	25	902
L	2	13	0	0	7	1	0	23
M	21	63	9	1	74	10	1	179
N	5	58	1	0	0	23	0	87
W	6	39	2	0	27	0	0	74
Grand Total	342	1,508	253	8	805	918	33	3,867

 

How can I do that in ArcGIS Dashboard, please your support.

The Excel sheet is in the attachment file.

Test.xlsx

JoseBarrios1

The standard ArcGIS Online Dashboard “Grouped Values” only gives totals per single field, and “Features” just lists each feature — so you need to use a data expression (Arcade) to create the summary table. 

 

 

Arcade: 

// Load the layer

var fs = FeatureSetByPortalItem(

    Portal('https://www.arcgis.com'),

    '70f5d3269a...your ID',

    0,

    ['Province', 'Status'],

    false

);

// Get distinct values

var provinces = Distinct(fs, 'Province');

var statuses = Distinct(fs, 'Status');

// --- Build dynamic fields list ---

var fields = [

    { name: "Province", type: "esriFieldTypeString" }

];

for (var s in statuses) {

    Push(fields, {

        name: s.Status,

        type: "esriFieldTypeInteger"

    });

}

// Add grand total column

Push(fields, {

    name: "GrandTotal",

    type: "esriFieldTypeInteger"

});

// Build schema

var schema = {

    fields: fields,

    geometryType: "",

    features: []

};

// Track totals for each status across all provinces

var overallTotals = {};

// Initialize overall totals

for (var s in statuses) {

    overallTotals[s.Status] = 0;

}

// --- Loop through provinces and build rows ---

for (var p in provinces) {

    var provName = p.Province;

    var attrs = { "Province": provName };

    var rowTotal = 0;

    for (var s in statuses) {

        var statusName = s.Status;

        var cnt = Count(

            Filter(fs, "Province = @provName AND Status = @statusName")

        );

        attrs[statusName] = cnt;

        rowTotal += cnt;

        overallTotals[statusName] += cnt;

    }

    // Add grand total column

    attrs["GrandTotal"] = rowTotal;

    // Push row

    Push(schema.features, { attributes: attrs });

}

// --- Add GRAND TOTAL ROW ---

var finalRow = { "Province": "GRAND TOTAL" };

var finalSum = 0;

for (var s in statuses) {

    var statusName = s.Status;

    var totalStatus = overallTotals[statusName];

    finalRow[statusName] = totalStatus;

    finalSum += totalStatus;

}

finalRow["GrandTotal"] = finalSum;

// Add to the table

Push(schema.features, {

    attributes: finalRow

});

return FeatureSet(schema);