> ## Documentation Index > Fetch the complete documentation index at: https://lightdash-mintlify-ca973f84.mintlify.site/llms.txt > Use this file to discover all available pages before exploring further. # 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 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) ## 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