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 · Accepted solution]

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'}
  ]
)

[ScottFortman1]

This is great, thank you!  Would you be able to to show how to reconstruct the date field?  Also once I have those recosntructed date values, i just need the total count of those values.  The results from your code shows the count for each line on that date.  Does that make sense?

[ScottFortman1]

Ok so I feel I'm close on this.  I used the code you gave me, modifed it to return the total flush lengths of pipe divided by the number of ROWIDs to get the daily average length of pipes flushed.  

The issue I am getting is that the count for ROW__ID is way off, currently there are 21 in the output, but only 7 show up in the count.  Also when I click done, the indicator is unable to execute script, even though the test results return a value.  Any thoughts and also, really do appreciate the help you already gave!

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'],True), flush2023) ;
var flushlength = LengthGeodetic(main2023, 'm');

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: 'length', expression: 'Shape__Length', statistic: 'SUM'}
  ]
)

return sum(flushlength/count('ROW_ID'))

[ScottFortman1]

Ok I got it working!  I actually just changed the statistic on your last line to "SUM", so that gave me the daily total feet flushed.  Then I just used the average function in the Indicator tool and there is my answer.  Sorry for all the replies and thanks again!

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) ;

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: 'length', expression: 'Shape__Length', statistic: 'SUM'}
  ]
)

[rwrenner_esri]

You can put the dates into an array (using the split function, similar to what's outlined here).

Then, using the distinct function, return just the distinct dates.

On the distinct dates output, you can use the count function to get the number of distinct dates.

Hope this helps!

[ScottFortman1]

Thank you!  I am trying out the previous solution first, but will also try this out.