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://xxxxx.xx.gov/portal'), 'xxx', 0, ['Length', 'SURV_DATE', 'EXEMPT_COD'], false);
var sum_flexible = Sum(Filter(fs_flexible, sql), 'Length')

var fs_rigid = FeatureSetByPortalItem(Portal('https://xxxxx.xx.gov/portal'), 'xxx', 1, ['Length', 'SURV_DATE', 'EXEMPT_COD'], false);
var sum_rigid = Sum(Filter(fs_rigid, sql), 'Length')

var fs_concrete = FeatureSetByPortalItem(Portal('https://xxxxx.xx.gov/portal'), 'xxx', 2, ['Length', 'SURV_DATE', 'EXEMPT_COD'], false);
var sum_concrete = Sum(Filter(fs_concrete, sql), 'Length')

var fs_gravel = FeatureSetByPortalItem(Portal('https://xxxxx.xx.gov/portal'), 'xxx', 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 · Accepted solution]

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

[KathrynWesson]

Fantastic, you're awesome! I simply replaced line 1 and line 2 with the following expressions:

var wkday = Weekday(Today())
var sql = "(SURV_DATE > CURRENT_DATE - @wkday) And (EXEMPT_COD IS NULL Or EXEMPT_COD = 2 Or EXEMPT_COD = 4)"

 Didn't have to change anything else from the original script, and it worked perfectly! Thank you so much!