Dashboard Data Expression to Combine Multiple Feature Sets and GroupBy Common Field

KathrynWesson

Hi everyone! I have another question regarding one of my data expressions in ArcGIS Dashboards.

I have four layers published to a feature service, which I'm trying to combine by their common field 'team name' and use in a Pie Chart to show total length surveyed by each team. Ideally, the expression will return a feature set that I can use in the Pie Chart function like this:

I think I have to use the GroupBy() function somewhere in my data expression, and maybe also add another line to my dictionary, but I'm not sure how I would do that/incorporate all of that with more than one feature set. Here is my data expression so far:

 

var sql = "EXEMPT_COD IS NULL Or EXEMPT_COD = 2 Or EXEMPT_COD = 4"

var fs_flexible = FeatureSetByPortalItem(Portal('https://xyz.xyz.gov/portal'), 'xyz', 0, ['Length', 'SURV_CREW', 'EXEMPT_COD'], false);
var sum_flexible = Sum(Filter(fs_flexible, sql), 'Length')

var fs_rigid = FeatureSetByPortalItem(Portal('https://xyz.xyz.gov/portal'), 'xyz', 1, ['Length', 'SURV_CREW', 'EXEMPT_COD'], false);
var sum_rigid = Sum(Filter(fs_rigid, sql), 'Length')

var fs_concrete = FeatureSetByPortalItem(Portal('https://xyz.xyz.gov/portal'), 'xyz', 2, ['Length', 'SURV_CREW', 'EXEMPT_COD'], false);
var sum_concrete = Sum(Filter(fs_concrete, sql), 'Length')

var fs_gravel = FeatureSetByPortalItem(Portal('https://xyz.xyz.gov/portal'), 'xyz', 3, ['Length', 'SURV_CREW', 'EXEMPT_COD'], false);
var sum_gravel = Sum(Filter(fs_gravel, sql), 'Length')

var sumDict = {
    'fields': [{'name':'Length_Miles', 'type':'esriFieldTypeDouble'}],
    'geometryType': '',
    'features':
    [{'attributes':
     {'Length_Miles': GroupBy((sum_flexible + sum_rigid + sum_concrete + sum_gravel), ['SURV_CREW'], [ { name: 'lengthbyteamname', expression: 'sumlengthbyteamname', statistic: 'SUM' } ])
     }}]};

return FeatureSet(Text(sumDict));

 

Do I need to 'flip' my order of operations so that the GroupBy() function occurs up in lines 4, 7, 10, and 13 and the Sum() function from those lines occurs down in my dictionary attributes? If so, how would I alter my script to achieve this? Additionally, how can I change it to get the results I want?

Thanks for any help!

Soobin-Choi

Hi Kathryn, you may need to swap your operations and aggregate your data first before combining the results into one feature set.

Refer to this sample here: https://github.com/Esri/arcade-expressions/blob/master/dashboard_data/CombineMultipleLayers(SerialChart).md

In your case, the expression would look something like this: 

 

 

var sql = '<INSERT SQL STATEMENT>'

var fs_flexible = FeatureSetByPortalItem(...)
var fs_rigid = FeatureSetByPortalItem(...)
var fs_concrete = FeatureSetByPortalItem(...)
var fs_gravel = FeatureSetByPortalItem(...)

var sum_flexible = Sum(Filter(fs_flexible, sql), 'Length')
var sum_rigid = Sum(Filter(fs_rigid, sql), 'Length')
var sum_concrete = Sum(Filter(fs_concrete, sql), 'Length')
var sum_gravel = Sum(Filter(fS_gravel, sql), 'Length')

var features = []

var feat_flexible = {
attributes: {
<INSERT ATTRIBUTE> 
}
}
Push(features, feat_flexible)

var feat_rigid = {
attributes: {
<INSERT ATTRIBUTE> 
}
}
Push(features, feat_rigid)

var feat_concrete= {
attributes: {
<INSERT ATTRIUTE> 
}
}
Push(features, feat_concrete)

var feat_gravel = {
attributes: {
<INSERT ATTRIBUTE> 
}
}
Push(features, feat_gravel)

var sumDict = {
fields: [
<INSERT FIELDS>
],
geometryType: '',
features: features
}

return FeatureSet(Text(sumDict))