List Data Expression with Arcade

LindseyStone

I'm trying to create a data expression in the List of a Dashboard to pull statistics from the different status of several features.  I have set the Group By expression on my status field and calculated the SUM on many of the fields and have that working.  The part I can't figure out is I want to determine the duration taken to complete the entire project for each category.  So I need to take the lowest start date from all the features in said category and the highest end date from the same category and Difference them.  So in each category I know how many days it took overall.  Note, some features start date or end date may overlap as we would have one person starting and ending in one area, and another person starting and ending in a different area at different times.

This is what I have so far, I just don't how to fit the variable durationcalc into the group by so it returns it as part of the table for each category.

//Define Portal Item and Fields
var fs = FeatureSetByPortalItem(Portal('portal'), 'item', feature, ['surveystatus','belowleaks1','belowleaks2','belowleaks3','aboveleaks1','aboveleaks2','aboveleaks3','footagemains','numberservices','startdate','enddate'],false);
//define duration
var sdate = Min(fs, 'startdate');
var edate = Max(fs, 'enddate');
var durationcalc = DateDiff(edate, sdate, 'days');
//Group By Survey Status and calc Sums on leaks
return GroupBy(fs, ['surveystatus'], [ 
    { name: 'Underground Leaks Class 1', expression: 'belowleaks1', statistic: 'SUM' }, 
    { name: 'Underground Leaks Class 2', expression: 'belowleaks2', statistic: 'SUM' },
    { name: 'Underground Leaks Class 3', expression: 'belowleaks3', statistic: 'SUM' },
    { name: 'Aboveground Leaks Class 1', expression: 'aboveleaks1', statistic: 'SUM' },
    { name: 'Aboveground Leaks Class 2', expression: 'aboveleaks2', statistic: 'SUM' },
    { name: 'Aboveground Leaks Class 3', expression: 'aboveleaks3', statistic: 'SUM' },
    { name: 'Miles of Main', expression: 'footagemains', statistic: 'SUM' },
    { name: 'Num of Services', expression: 'numberservices', statistic: 'SUM' },
    //Determine the earliest start date of all features and the latest end date of all features
    { name: 'Start Date', expression: 'startdate', statistic: 'MIN' },
    { name: 'End Date', expression: 'enddate', statistic: 'MAX' },])

 

jcarlson

So, you will probably have trouble getting a duration out of this expression directly. But you don't need the duration to be in the table in order to show it!

Given that you have a start and end date correctly being calculated for each category, you can use Arcade advanced formatting in the list configuration itself to show the DateDiff there.

In the advanced formatting:

return {
  attributes: {
    duration: DateDiff($datapoint['End Date'], $datapoint['Start Date'], 'days')
  }
}

 

And in the list configuration, reference {expressions/duration} to bring that value into what is shown.