If the records are always created in sequential order, then you can use the auto-enter function of "use value from last record" to store the date from last week in a new field (say FieldB), then use an auto-enter date field in another field (FieldA) to determine the date the current record was created (or modified to your requirement).
Then it would be a simple matter of FieldA - FieldB as an auto-enter calculation (uncheck "replace if existing value" so it updates whenever A/B is updated), into a new field for your result.
If the records are non-sequential, just turn the FieldB auto-enter off, and figure out how to populate it when the new record is created. Everything else is the same.
Use a relationship or ExecuteSQL to reference the data in the previous record with a date of the same day of the week - 7 days. If using ExecuteSQL, you will have to convert the text returned into a date before you subtract. Many use a custom function for this to make the expression a bit simpler.
Thanks Phil - I'm sure this is miles out but first attempt...
mon_gross - ExecuteSQL ( "select (fri_gross) from weekly_figures 2 WE2 join weekly_figures WE on WE2.id=WE.id where weekly_figures 2::date=date-7"; ""; ""; date-7)
You have spaces in your table names, so you need to escape those spaces for using in sql-syntax.
FROM this is a table name with spaces
Needs to be:
\”this is a table name with spaces\”
The defacto guide for learning all about ExecuteSQL() is here and is a highly suggested read: