Date Filter

Knowledge Checkpoint

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

• Relative Date Syntax :::

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

Date filters will recognise 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. :::

What is Date Filter?

Date Filter allows you to filter your data using conditions on time values.

Date Filter can be applied to fields of both Date and Datetime data types.

Basic date comparison operators

Holistics provides a few basic date comparison operators:

• is on

  This operator selects the exact date. If my date filter is is on 2021-07-05, only 2021-07-05 would be selected.

• last

  By default, this operator selects historical data and calculates the time range based on the complete time period, which means it will exclude the current incomplete period.

  • For example: If today is 2021-06-20 (June 20th 2021) then last 3 days would include June 17th, 18th, and 19th. The current day, June 20th, is excluded.

  However, we also provide an option to include the incomplete period called “Up to now / Up to today”

• next

  This operator selects future data and calculates the time range based on the complete time period, which means it will exclude the current incomplete period by default.

  • For example: If today is 2021-06-20 (June 20th 2021) then next 3 days would include June 21st, 22nd, and 23rd. The current day, June 20th, is excluded.

  However, we also provide an option to include the incomplete period called “From now / From today”.

• between

  This operator includes the selected date boundaries. If my date filter is between 2021-10-08 and 2021-10-10, the result would consist of 3 days: October 8th, 9th, and 10th.

• before

  This operator selects historical data that precedes the selected date. If my date filter is before 2021-07-05, every date that precedes2021-07-05 would be selected, excluding 2021-07-05.

• after

  This operator selects future data that comes after the selected date. If my date filter is after 2021-07-05, every date that follows 2021-07-05 would be selected, excluding 2021-07-05.

• is null

  This operator selects all dates that are unavailable (null). If my date filter is is null on a record like below:

  | Sales item | Date       |
  | ---------- | ---------- |
  | Shoes      | 2021-07-01 |
  | Hats       | 2021-07-12 |
  | Scarf      | null       |

  then only the date on the last row would be selected.

• is not null

  This operator selects all dates that are available (not null). If my date filter is is not null on a record like below:

  | Sales item | Date       |
  | ---------- | ---------- |
  | Shoes      | 2021-07-01 |
  | Hats       | 2021-07-12 |
  | Scarf      | null       |

  then every date except for the last row would be selected.

The following section describes in details 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.

Last, Next

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

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

Important Notes

1. The filtering time period defaults to be a complete period according to the time unit

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

• is not equivalent to 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: If today is June 15th, you will get data of March, April, May, and no data of June.

2. But, you have an option to include the current partial (incomplete) period for 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 the current date.

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

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

Before, After

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

Between

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

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

Is on

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

Matches

matches is a special operator that can smartly translate time expressions in natural language to exact date ranges. Note that the matches operator resolves condition values at runtime, not when saving the report.

Examples of valid syntax:

• yesterday
• monday last week
• last 2 months
• last 3 months - today
• 3 weeks ago till today
• 300 days ago - 7 days ago
• next 3 years in the future
• 19:00 yesterday to now
• Dec 2018 - 1/1/2020
• 1998 - 2018

The matches operator can be used for exact date matching and date range matching.

Exact date

Both absolute and relative date values are acceptable.

Format	Description	Example
DD MMM YYYY, MMM DD YYYY		01 April 2020, April 01 2020
DD-MM-YYYY, YYYY-MM-DD		01-04-2020, 2020-04-01
DD/MM/YYYY, YYYY/MM/DD		01/04/2020, 2020/04/01
today, yesterday, tomorrow
[weekday] this week		monday this week, wed this week
[weekday] next/last x week(s)		tuesday last 2 weeks, fri last 3 weeks
[time period] begin/end	The first/last day of the time period. Time period can include: - this year/ month/ week - last x year(s)/ month(s)/ week(s) - next x year(s)/ month(s)/ week(s)	Today is Mar 15, 2020.last month begin will translate to Feb 01, 2020.
x day(s) ago		1 day ago, 2 days ago
beginning	This equal to “1970-01-01” which is the earliest day for our system

Date range

The following table details the date range matching syntax and their behaviors.

Format	Description	Example
MMM YYYY	Cover all dates in a month.	April 2020, Mar 2020
YYYY	Cover all dates in a year.	2019, 2020
[start date] -/to [end date]	Cover all dates in range, including end date. [date] accepts both absolute and relative values. Note: there must be spaces around the hyphen	2020-03-01 to 2020-04-01, last month end - today
[start date] till/until [end date]	Cover all dates in range, excluding end date.	2020-03-01 till 2020-04-01, last month end until today, 60 days ago until 8 days ago
last x year(s)/ month(s)/ week(s)/ day(s)	Cover all dates in the previous x units, except the current year/ month/ week/ date.	Today is Mar 15 2020. last 2 years covers 2018 and 2019, last 2 months covers Jan and Feb 2020
x year(s)/ month(s) /week(s) ago	Cover all dates in a single year/month/week at x units before the current time.	Today is Mar 15 2020. 2 months ago covers the whole January 2020, 2 years ago covers 2018.
this year/month/week	Cover all dates within the same year/month/week with the current time.	Today is Mar 15 2020. this month covers the whole March 2020, this year covers 2020.
next x year(s)/ month(s)/ week(s)/ day(s)	Cover all dates in the next x units, except the current year/month/week/date	Today is Mar 15 2020. next 2 years cover 2021 and 2022, next 2 days cover Mar 16 and 17.

Note: We only support Date range matching at the moment. Please leave us a note if Time range matching is critical to you.

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