Filters reference

Filters appear at the top of the Explore view and allow users to change the data being pulled in.

Using filters

To learn more about using filters, check out our docs on limiting data using filters.

Filter types

Numeric filters

Filter	logic
is null	Only pulls in rows where the values are null for the field selected.
is not null	Only pulls in rows where the values are not null for the field selected.
is	Only pulls in rows where the values are equal to the values listed.
is not	Only pulls in rows where the values are not equal to the values listed.
is less than	Only pulls in rows where the values for the field selected are strictly less than the value listed.
is greater than	Only pulls in rows where the values for the field selectedare strictly greater than the value listed.

String filters

Filter	logic
is null	Only pulls in rows where the values are null for the field selected.
is not null	Only pulls in rows where the values are not null for the field selected.
is	Only pulls in rows where the values are equal to the values listed.
is not	Only pulls in rows where the values are not equal to the values listed. This filter explicitly includes NULL values. To exclude NULLs, add a separate is not null filter.
starts with	Only pulls in rows where the values for the field selected start with characters you’ve entered.
includes	Only pulls in rows where the values for the field selected includes the characters you’ve entered.
ends with	Only pulls in rows where the values for the field selected end with the characters you’ve entered.

Boolean filters

Filter	logic
is null	Only pulls in rows where the values are null for the field selected.
is not null	Only pulls in rows where the values are not null for the field selected.
is	Only pulls in rows where the values are equal to the values listed.

Date filters

Filter	logic
is null	Only pulls in rows where the values are null for the field selected.
is not null	Only pulls in rows where the values are not null for the field selected.
is	Only pulls in rows where the values are equal to the values listed.
is not	Only pulls in rows where the values are not equal to the values listed.
in the last	Only pulls in rows where the dates for the field selected are in the last time period you entered: “in the last 3 days”, “in the last 2 completed weeks”, “in the last 3 quarters” etc.
not in the last	Only pulls in rows where the dates for the field selected are not in the last time period you entered.
in the next	Only pulls in rows where the dates for the field selected are in the next time period you entered: “in the next 3 days”, “in the next 2 completed weeks”, “in the next 3 quarters” etc.
not in the next	Only pulls in rows where the dates for the field selected are not in the next time period you entered.
in the current	Only pulls in rows where the dates for the field selected are in the current time period you entered: “in the current day”, “in the current week”, “in the current quarter” etc.
not in the current	Only pulls in rows where the dates for the field selected are not in the current time period you entered.
is period-to-date	Only pulls in rows where the dates for the field selected are at or before the current position within their period, across all periods (e.g. “in the month-to-date” returns rows up to today’s day-of-month for every month, not just this month). Supports week, month, quarter, and year. See In the period-to-date.
is before	Only pulls in rows where the dates for the field selected are strictly before the date you entered.
is on or before	Only pulls in rows where the dates for the field selected are on or before the date you entered.
is after	Only pulls in rows where the dates for the field selected are strictly after the date you entered.
is on or after	Only pulls in rows where the dates for the field selected are on or after the date you entered.
is between	Only pulls in rows where the dates for the field selected are on or between the dates you entered: “between 2001-12-23 and 2003-01-02”.

Date/Timestamp Filter Reference Guide

The below examples show possible date/timestamp filter combinations and their corresponding SQL outputs. All examples use BigQuery syntax and assume:

• Current timestamp: 2025-10-24 15:30:00
• Example field: orders.created_at
• Timezone: UTC
• Week starts on Monday

Timestamp filter examples

Current Period Filters

Current (In The Current)

Filter	SQL Output
Current minute	orders.created_at >= TIMESTAMP('2025-10-24 15:30:00') AND orders.created_at < TIMESTAMP('2025-10-24 15:31:00')
Current hour	orders.created_at >= TIMESTAMP('2025-10-24 15:00:00') AND orders.created_at < TIMESTAMP('2025-10-24 16:00:00')
Current day	orders.created_at >= TIMESTAMP('2025-10-24 00:00:00') AND orders.created_at < TIMESTAMP('2025-10-25 00:00:00')
Current week	orders.created_at >= TIMESTAMP('2025-10-21 00:00:00') AND orders.created_at < TIMESTAMP('2025-10-28 00:00:00')
Current month	orders.created_at >= TIMESTAMP('2025-10-01 00:00:00') AND orders.created_at < TIMESTAMP('2025-11-01 00:00:00')
Current quarter	orders.created_at >= TIMESTAMP('2025-10-01 00:00:00') AND orders.created_at < TIMESTAMP('2026-01-01 00:00:00')
Current year	orders.created_at >= TIMESTAMP('2025-01-01 00:00:00') AND orders.created_at < TIMESTAMP('2026-01-01 00:00:00')

Past Period Filters

Last N Periods (in the last)

Filter	SQL Output
Last 1 minute	orders.created_at >= TIMESTAMP('2025-10-24 15:29:00') AND orders.created_at <= TIMESTAMP('2025-10-24 15:30:00')
Last 1 hour	orders.created_at >= TIMESTAMP('2025-10-24 14:30:00') AND orders.created_at <= TIMESTAMP('2025-10-24 15:30:00')
Last 1 day	orders.created_at >= TIMESTAMP('2025-10-23 15:30:00') AND orders.created_at <= TIMESTAMP('2025-10-24 15:30:00')
Last 1 week	orders.created_at >= TIMESTAMP('2025-10-17 15:30:00') AND orders.created_at <= TIMESTAMP('2025-10-24 15:30:00')
Last 1 month	orders.created_at >= TIMESTAMP('2025-09-24 15:30:00') AND orders.created_at <= TIMESTAMP('2025-10-24 15:30:00')
Last 1 quarter	orders.created_at >= TIMESTAMP('2025-07-24 15:30:00') AND orders.created_at <= TIMESTAMP('2025-10-24 15:30:00')
Last 1 year	orders.created_at >= TIMESTAMP('2024-10-24 15:30:00') AND orders.created_at <= TIMESTAMP('2025-10-24 15:30:00')

Last N Completed Periods (in the last, Completed)

Filter	SQL Output
Last 1 completed minute	orders.created_at >= TIMESTAMP('2025-10-24 15:29:00') AND orders.created_at < TIMESTAMP('2025-10-24 15:30:00')
Last 1 completed hour	orders.created_at >= TIMESTAMP('2025-10-24 14:00:00') AND orders.created_at < TIMESTAMP('2025-10-24 15:00:00')
Last 1 completed day	orders.created_at >= TIMESTAMP('2025-10-23 00:00:00') AND orders.created_at < TIMESTAMP('2025-10-24 00:00:00')
Last 1 completed week	orders.created_at >= TIMESTAMP('2025-10-13 00:00:00') AND orders.created_at < TIMESTAMP('2025-10-20 00:00:00')
Last 1 completed month	orders.created_at >= TIMESTAMP('2025-09-01 00:00:00') AND orders.created_at < TIMESTAMP('2025-10-01 00:00:00')
Last 1 completed quarter	orders.created_at >= TIMESTAMP('2025-07-01 00:00:00') AND orders.created_at < TIMESTAMP('2025-10-01 00:00:00')
Last 1 completed year	orders.created_at >= TIMESTAMP('2024-01-01 00:00:00') AND orders.created_at < TIMESTAMP('2025-01-01 00:00:00')

Future Period Filters

Next N Periods (In The Next)

Filter	SQL Output
Next 1 minute	orders.created_at >= TIMESTAMP('2025-10-24 15:30:00') AND orders.created_at <= TIMESTAMP('2025-10-24 15:31:00')
Next 1 hour	orders.created_at >= TIMESTAMP('2025-10-24 15:30:00') AND orders.created_at <= TIMESTAMP('2025-10-24 16:30:00')
Next 1 day	orders.created_at >= TIMESTAMP('2025-10-24 15:30:00') AND orders.created_at <= TIMESTAMP('2025-10-25 15:30:00')
Next 1 week	orders.created_at >= TIMESTAMP('2025-10-24 15:30:00') AND orders.created_at <= TIMESTAMP('2025-10-31 15:30:00')
Next 1 month	orders.created_at >= TIMESTAMP('2025-10-24 15:30:00') AND orders.created_at <= TIMESTAMP('2025-11-24 15:30:00')
Next 1 quarter	orders.created_at >= TIMESTAMP('2025-10-24 15:30:00') AND orders.created_at <= TIMESTAMP('2026-01-24 15:30:00')
Next 1 year	orders.created_at >= TIMESTAMP('2025-10-24 15:30:00') AND orders.created_at <= TIMESTAMP('2026-10-24 15:30:00')

Next N Completed Periods (In The Next, Completed)

Filter	SQL Output
Next 1 completed minute	orders.created_at >= TIMESTAMP('2025-10-24 15:31:00') AND orders.created_at < TIMESTAMP('2025-10-24 15:32:00')
Next 1 completed hour	orders.created_at >= TIMESTAMP('2025-10-24 16:00:00') AND orders.created_at < TIMESTAMP('2025-10-24 17:00:00')
Next 1 completed day	orders.created_at >= TIMESTAMP('2025-10-25 00:00:00') AND orders.created_at < TIMESTAMP('2025-10-26 00:00:00')
Next 1 completed week	orders.created_at >= TIMESTAMP('2025-10-27 00:00:00') AND orders.created_at < TIMESTAMP('2025-11-03 00:00:00')
Next 1 completed month	orders.created_at >= TIMESTAMP('2025-11-01 00:00:00') AND orders.created_at < TIMESTAMP('2025-12-01 00:00:00')
Next 1 completed quarter	orders.created_at >= TIMESTAMP('2026-01-01 00:00:00') AND orders.created_at < TIMESTAMP('2026-04-01 00:00:00')
Next 1 completed year	orders.created_at >= TIMESTAMP('2026-01-01 00:00:00') AND orders.created_at < TIMESTAMP('2027-01-01 00:00:00')

Within Custom Range

Filter	SQL Output
Between 2 dates	orders.created_at >= TIMESTAMP('2025-10-01 00:00:00') AND orders.created_at <= TIMESTAMP('2025-10-31 23:59:59')
On exact date	orders.created_at >= TIMESTAMP('2025-10-24 00:00:00') AND orders.created_at < TIMESTAMP('2025-10-25 00:00:00')

Notes

1. Completed periods always:

   • Start at the beginning of a period (00:00:00)
   • End at the beginning of the next period
   • Don’t include partial periods

2. Rolling periods (non-completed):

   • Use the current time as the reference point
   • Look backward/forward the specified amount
   • Include partial periods

3. Current periods:

   • Always start at the beginning of the current period
   • End at the beginning of the next period
   • Example: Current month starts at 1st of the month

4. Week handling:

   • By default, weeks start on the default day configured in your database
   • You can configure this to a different day in your database connection settings
   • Week boundaries are always at midnight (00:00:00)

Is period-to-date

The is period-to-date filter returns rows at or before the current position within their period, across every period in your data. Pick a unit — week, month, quarter, or year — and Lightdash filters each period down to the same relative point as today.

For example, if today is the 15th of the month, in the month-to-date:

• Includes rows dated on or before the 15th of any month (Jan 1–15, Feb 1–15, Mar 1–15, …), not just this month.
• Is designed for like-for-like comparisons across periods, such as this MTD versus the same MTD in prior months.

When to use in the period-to-date vs. in the current:

You want to…	Use
Compare month-to-date, quarter-to-date, or year-to-date across periods	in the period-to-date
Restrict data to only the current period (this month, this quarter, etc.)	in the current

is period-to-date is only available on Explore, chart, and dashboard filters. It is not supported as a YAML metric filter in your dbt project.

Date filter examples

Current Period Filters

Current (In The Current)

Filter	SQL Output
Current day	orders.created_date = DATE('2025-10-24')
Current week	orders.created_date >= DATE('2025-10-21') AND orders.created_date < DATE('2025-10-28')
Current month	orders.created_date >= DATE('2025-10-01') AND orders.created_date < DATE('2025-11-01')
Current quarter	orders.created_date >= DATE('2025-10-01') AND orders.created_date < DATE('2026-01-01')
Current year	orders.created_date >= DATE('2025-01-01') AND orders.created_date < DATE('2026-01-01')

Past Period Filters

Last N Periods (in the last)

Filter	SQL Output
Last 1 day	orders.created_date >= DATE('2025-10-23') AND orders.created_date <= DATE('2025-10-24')
Last 7 days	orders.created_date >= DATE('2025-10-17') AND orders.created_date <= DATE('2025-10-24')
Last 30 days	orders.created_date >= DATE('2025-09-24') AND orders.created_date <= DATE('2025-10-24')
Last 90 days	orders.created_date >= DATE('2025-07-26') AND orders.created_date <= DATE('2025-10-24')
Last 365 days	orders.created_date >= DATE('2024-10-24') AND orders.created_date <= DATE('2025-10-24')

Last N Completed Periods (in the last, Completed)

Filter	SQL Output
Last 1 completed day	orders.created_date = DATE('2025-10-23')
Last 1 completed week	orders.created_date >= DATE('2025-10-13') AND orders.created_date < DATE('2025-10-20')
Last 1 completed month	orders.created_date >= DATE('2025-09-01') AND orders.created_date < DATE('2025-10-01')
Last 1 completed quarter	orders.created_date >= DATE('2025-07-01') AND orders.created_date < DATE('2025-10-01')
Last 1 completed year	orders.created_date >= DATE('2024-01-01') AND orders.created_date < DATE('2025-01-01')

Future Period Filters

Next N Periods (In The Next)

Filter	SQL Output
Next 1 day	orders.created_date >= DATE('2025-10-24') AND orders.created_date <= DATE('2025-10-25')
Next 7 days	orders.created_date >= DATE('2025-10-24') AND orders.created_date <= DATE('2025-10-31')
Next 30 days	orders.created_date >= DATE('2025-10-24') AND orders.created_date <= DATE('2025-11-23')
Next 90 days	orders.created_date >= DATE('2025-10-24') AND orders.created_date <= DATE('2026-01-22')
Next 365 days	orders.created_date >= DATE('2025-10-24') AND orders.created_date <= DATE('2026-10-24')

Next N Completed Periods (In The Next, Completed)

Filter	SQL Output
Next 1 completed day	orders.created_date = DATE('2025-10-25')
Next 1 completed week	orders.created_date >= DATE('2025-10-27') AND orders.created_date < DATE('2025-11-03')
Next 1 completed month	orders.created_date >= DATE('2025-11-01') AND orders.created_date < DATE('2025-12-01')
Next 1 completed quarter	orders.created_date >= DATE('2026-01-01') AND orders.created_date < DATE('2026-04-01')
Next 1 completed year	orders.created_date >= DATE('2026-01-01') AND orders.created_date < DATE('2027-01-01')

Within Custom Range

Filter	SQL Output
Between 2 dates	orders.created_date >= DATE('2025-10-01') AND orders.created_date <= DATE('2025-10-31')
On exact date	orders.created_date = DATE('2025-10-24')

Notes

1. Key differences from timestamp filters:

   • No time components in any filters
   • Single day comparisons use equality (=) instead of ranges
   • DATE() function used instead of TIMESTAMP()

2. Completed periods always:

   • Start at the beginning of a period
   • End at the beginning of the next period
   • Don’t include partial periods
   • For single days, use equality instead of ranges

3. Rolling periods:

   • Use the current date as the reference point
   • Count in full days (N days forward/backward)
   • Include the current date in the range

4. Current periods:

   • For single day: use equality
   • For longer periods: use standard ranges
   • Example: Current month is all days from 1st to last day

5. Week handling:

   • Weeks start on Monday by default
   • Can be configured to start on Sunday
   • Full days only, no time components