Dashboard Data Expression to Filter Data Within Past Week

KathrynWesson

Hello! I'm looking for some help with one of my data expressions in ArcGIS Dashboards.

I have four layers published to a feature service, which I'm trying to combine. The catch is that I only want the output feature set to include records from 'the past week.' Is there a way to do this using the Filter() function in a data expression?

My understanding is that the Arcade Filter() function uses SQL language for the query, and I can't figure out how to make a SQL query that parses the week number from my date field and compares it to the current week number. Below is an example of a successful script I'm using. I want to change the portion of line 1 where it says SURV_DATE IS NULL to essentially say WEEK(SURV_DATE) = WEEK(CURRENTDATE()). Is this even possible? If so, what's the correct translation? If not, how would I alter my script to achieve this?

var sql = "SURV_DATE IS NULL And (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_DATE', '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_DATE', '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_DATE', '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_DATE', 'EXEMPT_COD'], false);
var sum_gravel = Sum(Filter(fs_gravel, sql), 'Length')

var sumDict = {
    'fields': [{'name':'Completed_Miles', 'type':'esriFieldTypeDouble'}],
    'geometryType': '',
    'features':
    [{'attributes':
     {'Completed_Miles': (sum_flexible + sum_rigid + sum_concrete + sum_gravel)
     }}]};

return FeatureSet(Text(sumDict));

 Thanks in advance!

HuubZwart

If you can find the date for the start of the current week you can filter using that date. Using the Arcade functions Today and Weekday, you can subtract the current weekday from today's date using SQL's built-in CURRENT_DATE function, you will get the date of Sunday of the current week. If you filter your featureset comparing SURV_DATE to that date, you will only get the features you need. Something in this line: 

 

var wkday = Weekday(Today()) // gets the current weekday, starts at 0 on Sunday by default, add 1 to start on Monday etc

return Filter(fs_flexible, "SURV_DATE > CURRENT_DATE - @wkday")