# Filters and date ranges

> The operators and named date periods behind every filter in sanda, how a condition is read, how date windows begin and end, and which time zone they use.

Wherever you narrow a question in sanda, you are writing a condition: a field, a comparison and one or more values. This page is the reference for how those conditions read, the operators available, the named date periods, and how a date window is worked out. It ends with the filters on a search index, which use a smaller vocabulary of their own.

## Where filters appear

| Where | What it is |
|---|---|
| **query semantic fluid**, on the semantic fluid page | The **only rows where** strip under your selection, with **add a condition** |
| A report block | The conditions in the block's builder |
| A whole report | The **filters** button on the report canvas, which opens **filters · every block** |
| A push | **Which rows** in the push editor, which narrows the records a push sends values for. See [create a push](https://docs.sanda-os.com.au/push/create-a-push#which-rows) |
| cherry and connected assistants | The `conditions` of a query, with a named period in `relative` |
| A search index | Filters on the columns you kept beside the text. See [search filters](#search-filters) |

A **condition** narrows one question and is thrown away with it. A **named filter** on your [semantic fluid](https://docs.sanda-os.com.au/semantic-fluid) is different: a condition your team wrote down and signed off, so that "approved" means one thing in every question. You use a named filter by name. A condition is something you are doing right now.

## How a condition works

A condition has three parts.

- **The field.** A dimension by its name, or any column of a table on your semantic map. The picker searches both, so you do not have to turn a column into a dimension first.
- **The operator.** What to compare. The operators offered depend on the field's type.
- **The values.** How many depends on the operator.

sanda reads a field's type from the dimension's kind or the column's data type: **text**, **number**, **true or false**, or **date and time**. A date field is offered the date operators, so you are never asked to match a timestamp as if it were text.

Every condition on a question must hold at once. There is no "or" between conditions. To match either of two values, give one condition both values.

A condition takes up to 20 values, each up to 200 characters. A question takes up to 20 conditions, and a report holds up to 20 filters.

## Operators


| Operator | Reads as | Field types | Values |
| --- | --- | --- | --- |
| `equals` | is | text, number, true or false, date and time | One or more |
| `not_equals` | is not | text, number, true or false, date and time | One or more |
| `contains` | contains | text | One or more |
| `not_contains` | does not contain | text | One or more |
| `starts_with` | starts with | text | One or more |
| `ends_with` | ends with | text | One or more |
| `gt` | is more than | number | One |
| `gte` | is at least | number | One |
| `lt` | is less than | number | One |
| `lte` | is at most | number | One |
| `between` | is between | number | Two (from and to) |
| `in_range` | is within | date and time | Two (from and to) |
| `before` | is before | date and time | One |
| `on_or_after` | is on or after | date and time | One |
| `set` | has a value | text, number, true or false, date and time | None |
| `not_set` | is empty | text, number, true or false, date and time | None |


A few behaviours worth knowing:

- **is** with one value matches it. With several values it means "is one of them". In the console, separate several with commas.
- **is not** and **does not contain** keep rows where the field is empty. A row whose status was never set is not a row whose status is "paid", so excluding "paid" does not silently drop the blanks.
- **contains**, **starts with** and **ends with** ignore capitals. With several values they match any one of them. A `%` or `_` in your value is matched as itself.
- **is between** includes both ends.
- **is** on a date matches one exact moment. To match a whole day, use **is within** with that day as both ends.
- **has a value** and **is empty** take no value.

## Date ranges

### Both ends, and which one is excluded

A date window is **half-open**: it includes its start and excludes its end. July is from 1 July up to, but not including, 1 August. That means the whole of 31 July is in and the first moment of 1 August is out, whether the column holds a date, a timestamp or a timestamp with a time zone.

The alternative, an inclusive pair of dates, is a common date bug in reporting. A window ending "31 July" against a timestamp means "up to midnight at the start of the 31st", and a day of trading disappears without a word. sanda avoids it by design.

You do not need to think about this when you pick dates yourself. The **to** date you enter is the last day that is in the window, and the label beside the boxes says "both days included". sanda stores the next midnight behind the scenes.

### Named periods

Pick a period by name instead of typing dates. They appear as buttons under **is within**.


| Period | Reads as | Kind |
| --- | --- | --- |
| `today` | today | A single day |
| `yesterday` | yesterday | A single day |
| `tomorrow` | tomorrow | A single day |
| `last_7_days` | last 7 days | Rolling, to the last full day or month |
| `last_30_days` | last 30 days | Rolling, to the last full day or month |
| `last_90_days` | last 90 days | Rolling, to the last full day or month |
| `last_3_months` | last 3 months | Rolling, to the last full day or month |
| `last_6_months` | last 6 months | Rolling, to the last full day or month |
| `last_12_months` | last 12 months | Rolling, to the last full day or month |
| `this_week` | this week | The current period |
| `this_month` | this month | The current period |
| `this_quarter` | this quarter | The current period |
| `this_year` | this year | The current period |
| `last_week` | last week | The last full period |
| `last_month` | last month | The last full period |
| `last_quarter` | last quarter | The last full period |
| `last_year` | last year | The last full period |
| `next_7_days` | next 7 days | Ahead of today |
| `next_30_days` | next 30 days | Ahead of today |


The **Kind** column groups the periods: a single day, rolling, the current period, the last full period, and ahead of today. The conventions below say exactly where each period starts and ends:

- **`today`, `yesterday` and `tomorrow` are single days.**
- **The day-based rolling periods count whole days back from the start of today.** `last_7_days`, `last_30_days` and `last_90_days` end yesterday. `last_7_days` is the seven complete days ending yesterday. Today is a part-day, and including it would make a daily total look as if it fell off a cliff every morning.
- **The months periods are whole calendar months, ending with last month.** `last_6_months` asked in September is March to August: six months, each of them finished. `last_90_days` is the rolling alternative when you want a quarter's trading counted back from yesterday.
- **`this_month` is the whole calendar month**, not month to date. For history the two are the same. For anything dated forward, such as a due date, month to date would hide the rows you opened the question to find.
- **Weeks start on Monday.**
- **Quarters and years are calendar quarters and years**, January to March and so on. There is no financial year period. For 1 July to 30 June, press **pick the dates myself** and enter them.
- **`next_7_days` and `next_30_days` start tomorrow.**

Here is what each period means on Wednesday 30 September 2026:

| Period | Covers |
|---|---|
| `today` | 30 September |
| `yesterday` | 29 September |
| `tomorrow` | 1 October |
| `last_7_days` | 23 to 29 September |
| `last_30_days` | 31 August to 29 September |
| `last_90_days` | 2 July to 29 September |
| `this_week` | Monday 28 September to Sunday 4 October |
| `last_week` | 21 to 27 September |
| `this_month` | 1 to 30 September |
| `last_month` | 1 to 31 August |
| `last_3_months` | 1 June to 31 August |
| `last_6_months` | 1 March to 31 August |
| `last_12_months` | 1 September 2025 to 31 August 2026 |
| `this_quarter` | 1 July to 30 September |
| `last_quarter` | 1 April to 30 June |
| `this_year` | 1 January to 31 December 2026 |
| `last_year` | 1 January to 31 December 2025 |
| `next_7_days` | 1 to 7 October |
| `next_30_days` | 1 to 30 October |

The console prints the resolved days under the period buttons, so you can check a window before it goes into a board pack.

### Time zones

Which time zone a named period uses depends on where you set it.

- **In the console.** When you press a period in the semantic fluid workbench or a block's conditions, sanda works out the dates at that moment, in your browser's time zone, at your local midnight. The dates it worked out are what is sent.
- **In a report's filters.** A period set in **filters · every block** is kept as a period, not as dates, so the window moves with the calendar. sanda works it out on its own servers, in UTC, each time a block runs.
- **In a push.** A period under **Which rows** is kept as a period too, so a push that sends "last 30 days" every day sends a window that moves with it. sanda works it out in UTC each time the push runs.
- **Through cherry and connected assistants.** There is no browser, so a named period is worked out in UTC, and the result carries the dates it used. Ask cherry which dates it used if you need to see them.

UTC midnight is 10am in Sydney during standard time and 11am during daylight saving. So a window worked out in UTC starts and ends at that hour, and before it each morning, UTC is still on yesterday's date. "Yesterday", asked at 8am in Sydney, is the day before the Sydney yesterday. When the exact days matter, such as a month-end close, give explicit dates.

:::caution A block's own period is fixed at the moment you pick it
A period chosen in a single block's own conditions keeps the dates it meant when you picked it. A period in **filters · every block** stays a period and moves with the calendar. To have a whole report follow the calendar, set the window in its filters.
:::

### In reports

A condition on the **filters · every block** pane applies to every block. It replaces a block's own condition on the same field, so "last 12 months" there widens a block that said "last month". You can also type a sentence into **say it**, such as "only paid invoices" or "last 12 months", and sanda turns it into one condition for you. That uses AI, so it counts towards your [daily AI limit](https://docs.sanda-os.com.au/cherry/models-and-usage).

## Search filters

A [search index](https://docs.sanda-os.com.au/search) has filters of its own, on the columns you kept beside the text when you created it. They are a smaller vocabulary than conditions, and every filter must match.

| Column holds | Comparisons in the console | Name used by assistants |
|---|---|---|
| Text | **is**, **is not**, **contains** | `eq`, `neq`, `contains` |
| A number, or a date and time | **is**, **is not**, **greater than**, **at least**, **less than**, **at most** | `eq`, `neq`, `gt`, `gte`, `lt`, `lte` |
| True or false | **is**, **is not** | `eq`, `neq` |

- A search takes up to eight filters, each value up to 200 characters.
- On text, **is** matches the whole value exactly, capitals included, and **contains** ignores capitals. A text column cannot be compared with greater than or less than.
- Dates and times in the console are entered in your own time zone.
- Connected assistants can also use `in`, a list of up to 50 values.

See [search your records](https://docs.sanda-os.com.au/search/use-search).

:::links
- [Search your records](https://docs.sanda-os.com.au/search/use-search): Filters on a search index.
- [Build reports with cherry](https://docs.sanda-os.com.au/reports/build-with-cherry): Setting a window on a whole report.
- [Ask questions](https://docs.sanda-os.com.au/cherry/ask): Naming a period in a question to cherry.
:::