Date Filter

Knowledge Checkpoint

A grasp of these concepts will help you understand this documentation better:

• Natural Time Expression

Holistics provides several date filtering operators that can be used with Dashboard Filter, Report Conditions, and Email Schedule Filters.

Note

Date filters will recognize days in relation to the actively applied timezone. This may affect how reports will be seen by different viewers. Head over to Timezone Settings to learn more about the timezone settings available to your organization.

Introduction

Date Filter can be applied to fields of Date and Datetime data types. Currently we support the following date comparison operators:

Operator	Description	Example	Dates selected
is on	Select the exact date	is on 2024-01-01	2024-01-01
between	Select a date range between two dates, including the boundaries	between 2024-01-01 and 2024-01-03	2024-01-01, 2024-01-02, 2024-01-03
before	Select all dates before (not including) a particular date	before 2024-01-31	all dates up to 2024-01-30
after	Select all dates after (not including) a particular date	after 2024-01-01	all dates from 2024-01-02 onward
last	Select the previous N complete time periods. By default, the current incomplete time period is excluded, but can also be included.	Today is 2024-01-15, and we select last 3 months	All dates in the months: Oct 2023, Nov 2023, Dec 2023. Jan 2024 is excluded since it is incomplete.
next	Select the next N complete time periods. By default, the current incomplete time period is excluded, but can also be included.	Today is 2024-01-15, and we select next 3 months	All dates in the months: Feb 2024, Mar 2024, Apr 2024. Jan 2024 is excluded since it is incomplete.
matches	Select the dates matching a date expression based on natural language, like "yesterday", "last 3 months", "7 days ago"	Please click to the link for more detailed examples
is null	Select the records that do not have date data
is not null	Select all records that have date data

The following section describes in detail the behavior of each operator on your data.

Note: The samples below are from a PostgreSQL database. The syntax may differ from the database you are working with.

Is on

With the is on operator, you will get the data of the exact date that you selected

Example

Between

With between operator, data points that lie on the boundaries will be included:

Example

In case you would like to exclude data points that lie on the boundaries, you can use the matches operator instead.

Before, After

With before and after operators, any data points that lie on the date selected will be excluded:

Example

Last, Next

When selecting these operators, you can specify the number of time periods (day, week, month, year) to look back/look forward. For example:

• Last 180 days
• Last 3 months up to today
• Last 1 year

By default, the periods are resolved into complete time periods by Today. For example, Today is 2024-01-15, then December 2023 and February 2024 are considered complete months, and January 2024 is considered incomplete.

The following sections will go into more details of this concept.

Complete and incomplete time periods

For example, last 2 months will be resolved into "2 complete past months", which:

• is not equivalent to the last 60 days or last 61 days, etc., because different months have different numbers of days
• and does not include the current month because the current month is not complete.

For example, here we filter last 3 months: Today is June 15th, you will get data for March, April, and May, and no data for June.

Example

How to include current partial (incomplete) period

When using last operator:

• For “minutes” & “hours”: The option “Up to now” extends the end point of your selected time range up to this very second.
• For “days”, “weeks” or longer: The option “Up to today” extends the end point of your selected time range up to the end of today.

TIPS

You can also use matches operator to include the current partial time period.

For example, use last 3 months - today syntax to include both data of the past 3 complete months and this partial month up to Today.

For example, assuming that it’s 20:01:04 on June 30 right now, if we filter last 6 hours with the “Up to now” option, we will get data from 14:00:00 to 20:01:04 on June 30.

Example

Similarly, assuming that today is June 30, 2022, if we filter the last 3 years with the “Up to today” option, we will get data from 2019 to the end of today - June 30, 2022.

Example

Matches

matches is a special operator that can translate time expressions in natural language to the standard time filtering conditions in SQL.

The matches operator can be used for exact date, date range and time range matching. Examples of valid syntax:

• yesterday
• Monday last week
• last 2 months
• last 3 months - today
• last 3 hours to now
• next 3 years
• Dec 2018 - 1/1/2020
• 1998 - 2018 ...

For more information, please refer to the dedicated Natural Time Expression docs.

Note

The matches operator resolves condition values at runtime, not when saving the report.

Creating a Date Filter

You can create a Date filter in two ways:

1. At Filter Type, select Field filter, and point to a Date/ Datetime field in a model
2. Select the Date filter to create a manual input filter

Mapping a Date Filter

The Date Filter can only be mapped to widget fields of Date/Datetime type:

Only fields of Date type are available when selecting fields to map your filters to the widgets