Filters reference - Lightdash

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

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
Rolling periods (non-completed):
- Use the current time as the reference point
- Look backward/forward the specified amount
- Include partial periods
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
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)

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

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()
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
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
Current periods:
- For single day: use equality
- For longer periods: use standard ranges
- Example: Current month is all days from 1st to last day
Week handling:
- Weeks start on Monday by default
- Can be configured to start on Sunday
- Full days only, no time components

​Using filters

​Filter types

​Numeric filters

​String filters

​Boolean filters

​Date filters

​Date/Timestamp Filter Reference Guide

​Timestamp filter examples

​Current Period Filters

Current (In The Current)

​Past Period Filters

Last N Periods (in the last)

Last N Completed Periods (in the last, Completed)

​Future Period Filters

Next N Periods (In The Next)

Next N Completed Periods (In The Next, Completed)

Within Custom Range

​Notes

​Date filter examples

​Current Period Filters

Current (In The Current)

​Past Period Filters

Last N Periods (in the last)

Last N Completed Periods (in the last, Completed)

​Future Period Filters

Next N Periods (In The Next)

Next N Completed Periods (In The Next, Completed)

Within Custom Range

​Notes

Using filters

Filter types

Numeric filters

String filters

Boolean filters

Date filters

Date/Timestamp Filter Reference Guide

Timestamp filter examples

Current Period Filters

Past Period Filters

Future Period Filters

Notes

Date filter examples

Current Period Filters

Past Period Filters

Future Period Filters

Notes