Arcade Expression to count number of days work was done

ScottFortman1

Hi there,

disclaimer, I am not a developer, just an analyst limping my way through Arcade.

I am trying to create an indicator that displays the average length of feet our crews flush daily starting from a specific date.  Calculating the total length is easy but having trouble counting the actual days the work has occurred to help calculate the real average (I can't use weekends and days they don't flush, that would skew the average).  The layer I am using has date and time and since there are multiple flushes occurring in a day, when I try GroupBy i get multiple results per day.  I am getting stuck trying to convert the date field to text to see if that removes the time (I've seen other posts where that is supposed to work).

The code below works (except the fdate variable that I tried to plug into the dictionary, which I changed back to original date field for now)and returns a dictionary of all the flushed lines and their dates and times.  If someone can help get it to only show 1 record per date and get a count of how many dates there are, I will be eternally greatful!  

var tvwdportal = Portal('https://mapping.tvwd.org/portal')
var flush2023 = "LifeCycleStatus = 'ACTIVE' And OwnedBy = 'TVWD' And Diameter <= 12 And LastFlushDate >= date '2023-10-26'"
var main2023 = Filter(FeatureSetByPortalItem(tvwdportal,"b24772e427084f21b1f208a685d07fcc",48,['LastFlushDate','Shape__Length'],False), flush2023) ;

var fdate = Text('LastFlushDate', "Y-MM-DD")

var dict= {
   'fields':[{'name': 'FlushDay','type':'esriFieldTypeString'},
 {'name':'Length','type': 'esriFieldTypeDouble'}],
  'geometryType': '',
    'features': []};
   
  for (var d in main2023){
      push(dict['features'],{'attributes': {'flushday':d['LastFlushDate']}}
      )
  } 

return featureset(text(dict))

 

 

jcarlson

In GroupBy, you can use a SQL expression in the grouping fields, too, to get the different parts of the date and ignore the times. You can use the output fields to reconstruct an actual DateTime value if you needed.

GroupBy(
  main2023,
  [
    {name: 'theYear', expression: 'EXTRACT(YEAR FROM LastFlushDate)'},
    {name: 'theMonth', expression: 'EXTRACT(MONTH FROM LastFlushDate)'},
    {name: 'theDate', expression: 'EXTRACT(DAY FROM LastFlushDate)'}
  ],
  [
    {name: 'flushcount', expression: '1', statistic: 'COUNT'},
    {name: 'avglen', expression: 'Shape__Length', statistic: 'AVG'}
  ]
)