Working With Dates
There are a few things to note when working with Date Filter and Date Range Filter in Holistics.
#Global Time Zone
In Holistics, All schedules (email schedules, imports, data transforms, etc...) are based on your browser's timezone.
On the other hand, Relative date/time are calculated using the Global Time Zone that is set in the Settings page. This is the organization-wide timezone.
#Relative Date Syntax
When setting default values for date filters, we support flexible relative dates.
- 2 weeks ago
- 1 month ago
- 30 days ago
- 2 weeks from now
- last week Tuesday
- 3rd Wednesday in November
- last month end (the last day of the previous month)
- last month begin (the first day of the previous month)
We also support "begin" or "end" to indicate the beginning/end of each week/month/year.
#Different aggregation intervals in time-series
In a time-series report, you might want to group data by different time intervals such as daily, weekly, monthly, quarterly. For this purpose, you can create a
time_period filter and use it in a
DATE_TRUNC() (or equivalent) function. We will demonstrate how this combination works.
#Create time_period filter
We will create a Dropdown Filter named
time_period (1) with the following Manual Entries (2):
Finalize the filter by input default value 'week' (3) and checking Hide 'All' (4), then click on the Preview (5) button to see the result:
Use the filter in a
The query using PostgreSQL is:
If you use BigQuery's Standardsql:
When you select a time period value, the query will be modified so that
report_time is truncated to day, week, month, year... level.
The approach above works best in more complicated cases in SQL (accumulate SUM, growth rate, average over days, months, weeks...), while Date Drill can only handle simple SUM.
We currently support the following date format: