Arcade: Find date with known difference (subtract?)

Question

I have a time series table with a value field for each day. I need to create a expression to add a field showing the date from 4 days previous to the current date and the value from that earlier date. I see there is a DateAdd function but I kind of need the opposite of that. Not sure DateDiff will work because it's calculating the difference between two known dates. I know the current date and the difference, but the second date is unknown (and will change every day when the data updates). For example, 1) subtract 1/1/2020 - minus 4 days = 12/28/2019, 2) Find the value from 12/28/2019 and assign it to the expression to display in the pop up. Thanks for any ideas.

XanderBakker · Answer

Hi Sara Kidd , The DateAdd function can work with negative values. See example below: var dt = Now(); var fourdaysearlier = DateAdd(dt, -4, "days"); Console(dt); Console(fourdaysearlier); return fourdaysearlier;‍‍‍‍‍ This will write the following text to the console: 2020-09-09T16:23:42-05:00 2020-09-05T16:23:42-05:00‍‍ As you can see you will have a date time of 4 days before. However you will need to use a date range to select records that were taken four days ago. Have a look at the example below (I assume the sql can be simplified, but I don't have the data to test this): var dt = Now(); var fourdaysearlier = DateAdd(dt, -4, "days"); var format = "MM/DD/Y"; var fourdaysearliertxt = Text(fourdaysearlier, format); // let's assume your date field is called LASTUPDATE // LASTUPDATE BETWEEN timestamp '04/30/1999 00:00:00' and date '04/30/1999 23:59:59' var sql = "LASTUPDATE BETWEEN timestamp '" + fourdaysearliertxt + " 00:00:00' and date '" + fourdaysearliertxt + " 23:59:59'"; Console(dt); Console(fourdaysearlier); Console(fourdaysearliertxt); Console(sql); // filter featureset "timeseries" (should be defined earlier) // var fs = Filter(timeseries, sql); ‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍