Parse Duplicate days in a count in a Table

[ScottJones1]

I have grouped table, The Incidents might get multiple survey entries a day from different people. I just want to show how many days we have been on an incident. Is there a way to parse out duplicate days?

return {

  cells: {

    Incident_: {

      displayText : $datapoint.Incident_,

      textColor: '',

      backgroundColor: '',

      textAlign: 'left',

      iconName: '',

      iconAlign: '',

      iconColor: '',

      iconOutlineColor: ''

    },

    COUNT_DATE_OF_EVENT: {

      displayText : Text($datapoint.COUNT_DATE_OF_EVENT),

      textColor: '',

      backgroundColor: '',

      textAlign: 'right',

      iconName: '',

      iconAlign: '',

      iconColor: '',

      iconOutlineColor: ''

    }

  }

}

[Neal_t_k]

@ScottJones1 I think you will need a new expression for this as I am not sure counting the days will work since you can have multiple entries per day.  Another way of handling it would be to get the earliest submission and the last submission on an incident basis and then take the difference between those two dates for your duration.

var portal = Portal('https://www.arcgis.com');
var layer = FeatureSetByPortalItem(portal, itemId, layerIndex, [dateField, groupField]);

var groups = Distinct(layer, [groupField]);
var features = [];


for (var g in groups) {
    var groupValue = g[groupField];
    if (IsEmpty(groupValue)) continue;

    var filtered = Filter(layer, groupField + " = @groupValue");

    var start = First(Top(OrderBy(filtered, dateField + ' ASC'), 1));
    var end = First(Top(OrderBy(filtered, dateField + ' DESC'), 1));

    Push(features, {
        attributes: {
            Group: groupValue,
            StartDate: start[dateField],
            EndDate: end[dateField],
            Duration: DateDiff(end[dateField], start[dateField], 'days')
        }
    });
}

return FeatureSet(Text({
    fields: [
        { name: "Group", type: "esriFieldTypeString" },
        { name: "StartDate", type: "esriFieldTypeDate" },
        { name: "EndDate", type: "esriFieldTypeDate" },
        { name: "Duration", type: "esriFieldTypeDouble" }
    ],
    geometryType: "",
    features: features
}));

[ScottJones1]

Got an "Unable to execute Arcade script" , the field name for the date is 'date_of_event'. the field name for the Incidents is 'Incident_' not sure if I just missed an input or ???

started at Line 1 ends on Line 36

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

var layer = FeatureSetByPortalItem(portal, itemId, layerIndex, [date_of_event, Incident_]);

var groups = Distinct(layer, [Incident_]);

var features = [];

for (var g in groups) {

    var groupValue = g[Incident_];

    if (IsEmpty(groupValue)) continue;

    var filtered = Filter(layer, Incident_ + " = @groupValue");

    var start = First(Top(OrderBy(filtered, date_of_event + ' ASC'), 1));

    var end = First(Top(OrderBy(filtered, date_of_event + ' DESC'), 1));

    Push(features, {

        attributes: {

            Group: groupValue,

            StartDate: start[date_of_event],

            EndDate: end[date_of_event],

            Duration: DateDiff(end[date_of_event], start[date_of_event], 'days')

        }

    });

}

return FeatureSet(Text({

    fields: [

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

        { name: "StartDate", type: "esriFieldTypeDate" },

        { name: "EndDate", type: "esriFieldTypeDate" },

        { name: "Duration", type: "esriFieldTypeDouble" }

    ],

    geometryType: "",

    features: features

}));

[Neal_t_k]

@ScottJones1 There were quotes missing from the field names:

var portal = Portal('https://www.arcgis.com');
var layer = FeatureSetByPortalItem(portal, itemId, layerIndex, ['date_of_event', 'Incident_']);

var groups = Distinct(layer, ['Incident_']);
var features = [];


for (var g in groups) {
    var groupValue = g['Incident_'];
    if (IsEmpty(groupValue)) continue;

    var filtered = Filter(layer, 'Incident_' + " = @groupValue");

    var start = First(Top(OrderBy(filtered, 'date_of_event' + ' ASC'), 1));
    var end = First(Top(OrderBy(filtered, 'date_of_event' + ' DESC'), 1));

    Push(features, {
        attributes: {
            Group: groupValue,
            StartDate: start['date_of_event'],
            EndDate: end['date_of_event'],
            Duration: DateDiff(end['date_of_event'], start['date_of_event'], 'days')
        }
    });
}

return FeatureSet(Text({
    fields: [
        { name: "Group", type: "esriFieldTypeString" },
        { name: "StartDate", type: "esriFieldTypeDate" },
        { name: "EndDate", type: "esriFieldTypeDate" },
        { name: "Duration", type: "esriFieldTypeDouble" }
    ],
    geometryType: "",
    features: features
}));

Also I assume you left the itemid and layer index out on purpose, but be sure those are correct. 

[ScottJones1]

no, not sure where I locate the itmeid and layer index

[ScottJones1]

77fb11f20f7f4b689d38258d3b5b0f43&sublayer=0#data this is what I see when I click the field

77fb11f20f7f4b689d38258d3b5b0f43 this is for the entire layer...  I entered this in for itemId, 0, not sure what to enter for LayerIndex

var layer = FeatureSetByPortalItem(portal, itemId, layerIndex, ['date_of_event', 'Incident_']);

[Neal_t_k]

@ScottJones1 

itemid= '77fb11f20f7f4b689d38258d3b5b0f43'  (keep quotes)

layerIndex = 0  (no quotes)

[ScottJones1]

Neal, could you take one more look? I am getting the same numbers pre advanced formula, (before it was a blank screen) with the triangle !. Then 'Need' in Incident Reference should only be counting 2 days, it has multiple entries on 10/7 and only 1 entry on 10/8 but it is counting all entries. Could it be, that I do not have a time attached to the Survey123 Connect? for my entries, the time is not important but if it would make the formula I can add it..

[Neal_t_k]

@ScottJones1 I think you are still in the advanced editor, that code is for a new data expression which is basically making a new feature set from your existing data.

1. Add a new table to your dashboard.

2. when it asks where to get data from, click the new data expression button.

3: Paste the code, and hit "run" to test.  That should be a example of your new table.

[ScottJones1]

so close, if there are two or more entries sequentially, I think it would work. However if there is only one entry it comes out as Zero, if there are missing days (which there most likely will be a few) it counts those as part of the time on INC.. in the example below, an entry was made on the 3rd and not again until the 7th, but the table counts all the dates in-between.

If you have an idea, feel free to share, if not, I'll keep an eye out for a work around..