Lesson 3 of 8 · Lab

Rolling date filter: last 7 days from today

Date/Time Now makes a one-row table holding today's date. A rule that compares each row with it follows the calendar, so test the rule on a date you choose before you trust it.

Ahead of November, Marit asks for a last-7-days view that nobody has to retype. Before you promise it, test the rule.

This live flow in Strata, Oplema's browser app for data prep, holds September's finished hires at fictional Kramerica Bikes, one Manual Input per shop. A fourth Manual Input, a table typed into the flow, holds as_of: 2026-09-30. Lookup, set to Every Row, puts that date on every hire.

The rule keeps hire_on >= as_of - 7, up to as_of. How many days is that?

Eight. as_of - 7 is September 23, and >= keeps it, so September 23 to 30 pass.

Test the rule, then use today

Make Filter keep exactly the 7 days up to as_of.

Check: at 2026-09-30 it keeps 256 rows, the earliest on September 24. Prove it follows the date: at 2026-09-27 it keeps 266. Then replace the as_of table with Date/Time Now, giving a DATE column as_of, and explain what OUTPUT TRUE shows.

If you're stuck
  1. Filter is selected. Its OUTPUT TRUE has 292 rows, and the first are Harbour's hires on September 23: eight days.
  2. In Expression, change >= to > and select the Filter block. OUTPUT TRUE has 256 rows, September 24 to 30.
  3. Select the Manual Input below Union and change 2026-09-30 to 2026-09-27. Select Filter: 266 rows, September 21 to 27. The rule follows the date it's given.
  4. Select that Manual Input again and press Delete.
  5. From IO, drag Date/Time Now into its place. Set Format to DATE, then select the Column Name field, select all (Ctrl+A or Cmd+A) and type as_of.
  6. Drag from Date/Time Now's right dot to Lookup's lower left dot, then select Filter. OUTPUT TRUE is empty.

Swipe sideways to see the canvas, or open this page on a larger screen to work with it.

In an expression, press Esc to apply it and move on (press it twice if a suggestion list is open).

Why OUTPUT TRUE is empty

Today is later than every date in the file, which stops on September 30. The rule is right; the data is old. Select Lookup to see today's date in as_of.

Why the view stays off the page

A rule written against today is only as fresh as its file. In November this view would show an empty week until someone loads that week's exports, and the shops send their files once a month. So the answer to Marit is that the rule is tested and ready, and the view stays off the page until the files arrive weekly. On a dashboard, Vista Filters offers the same rule as Relative period, Last N days, and it has the same limit.

Your line for Marit: "A last-7-days view needs this week's files. Until they arrive weekly, it would show an empty week."

See the Date/Time Now reference. For later: Checking a dashboard after a refresh asks what fresh should mean.

Common rolling date mistakes

  • Counting the edge twice. >= with - 7 gives eight days. Check the earliest date that passes.
  • Testing only on today. You can't see whether the edge is right when nothing passes. Test on a date inside the data.
  • Forgetting the time zone. Date/Time Now uses UTC, so just after midnight in Europe it can still be yesterday.

Today's date in SQL and Excel

SELECT * FROM hires
WHERE hire_on > current_date - 7
  AND hire_on <= current_date;

current_date is DuckDB's Date/Time Now. In Excel, =TODAY()-7 recalculates every time the workbook opens, so a sheet built on it changes without anyone touching it.