Arcade: Find date with known difference (subtract?)

[SaraKidd]

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]

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

‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍