Custom calendar overview

With a custom calendar, you can choose when the year, quarter, or week starts, and search using date-related keywords.

You can create custom calendars for different purposes within your company, including:

  • Retail calendars, like 4-4-5, 4-5-4, or 5-4-4.

  • Fiscal calendars, where the year or quarter starts on a different date each year.

  • Pharmaceutical calendars, where the week runs from Friday through Thursday.

  • Calendars in different languages.

For an example custom calendar, see Customer calendar.

Options

In a custom calendar, you can set the following:

  • The starting date of a year

  • The starting dates of quarters

  • The starting day of the month

  • The starting day of the week

  • The words used for the days of the week, months and quarters

Custom calendar versus standard calendar

ThoughtSpot uses the ISO week date system to define the standard calendar. This means that the fiscal year contains 52 or 53 full weeks, with Monday defined as the start of the week.

When January 1st falls on a Monday, Tuesday, Wednesday, or Thursday, it is defined as part of week 01. If January 1st falls on a Friday, it is part of week 53 of the previous year. If it falls on a Saturday, it is part of the last week of the previous year (week 52 in a regular year and week 53 in a leap year). If it falls on a Sunday, it is part of week 52 of the previous year.

There are many reasons users may choose to set up a custom calendar rather than using the default calendar. These may include:

  • Changing the start of the week from Monday to another day.

  • Setting up alternative calendar types.

  • Introducing granular control for when dates should be included in date boundaries such as weeks, months, quarters, or years.

Supported cloud data warehouses

Custom calendar currently supports the following:

  • Amazon Athena

  • Amazon Aurora MySQL

  • Amazon Aurora PostgreSQL

  • Amazon RDS MySQL

  • Amazon RDS PostgreSQL

  • Amazon Redshift

  • Databricks

  • Google AlloyDB for PostgreSQL

  • Google BigQuery

  • Google Cloud SQL for PostgreSQL

  • Google Cloud SQL for SQL Server

  • Microsoft Azure Synapse

  • MySQL

  • Oracle ADW

  • PostgreSQL

  • Presto

  • SAP HANA

  • Singlestore

  • Snowflake

  • SQL Server

  • Starburst

  • Teradata

  • Trino

Search features

You can use your custom calendar to search the following ways:

  • Using date keywords, like this quarter and q3.

  • Using date formulas with the fiscal option specified. (See Fiscal and Gregorian calendars).

  • Overriding the calendar used in the search bar by typing your custom calendar’s name.

  • Binding a calendar with a column under the table detail page. Example: Binding Lineorder commitdate with the French calendar.

  • Specifying a calendar in the formula.

Limitations

Before you create a custom calendar, make sure to review the following limitations.

Creating a custom calendar

  • ThoughtSpot doesn’t support month offset when creating a custom calendar using the: 4-4-5, 4-5-4, or 5-4-4 calendar type configurations. Month offset is a separate calendar type.

  • When uploading files, the date format must be MM/DD/YYYY. No other formats are supported.

  • The file must be sorted based on the date in ascending order.

  • The file format should be UTF-8.

  • Custom calendar column headers must be lower-case.

  • You can now add row-level security rules to custom calendars. To enable this feature, contact ThoughtSpot Support.

Using a custom calendar

  • Formulas are limited to defining a single custom calendar.

  • VS supports multiple custom calendars as long as there is only a single calendar per clause.

Updating a custom calendar

The values in the generated calendar table can be updated to meet your specific business requirements. Before updating your calendar, be aware of the following:

  • day_of_week, month, quarter, and year are defined as varchar columns. The expected values are strings rather than numbers. If numbers are defined, the value may not be displayed as an indexed option.

    Examples:

    • day_of_week: Monday, Tuesday, Wednesday rather than day_of_week: 1, 2, 3

    • quarter: Q1, Q2 rather than quarter: 1, 2

    • end_of_[period]_epoch value should be the same as the start_of_[period]_epoch value for the next period. This is because the code is greater than or equal to the start value and less than the end value.

If you update a range of dates in the underlying table, you must manually refresh the custom calendar in ThoughtSpot by running the update command.

To update your custom calendar, do the following:

  1. Sign in to your ThoughtSpot cluster.

  2. Select Data in the top navigation bar.

    The Data workspace appears.

  3. Click Utilities.

  4. Click Add/modify custom calendar.

  5. Click the More menu more options menu icon for the custom calendar you want to update and select Update Calendar.

Prerequisites

  • For a user to create a custom calendar, they must have the required permissions to create a table in the database used in the connection where they want to create their custom calendar.

Custom calendar query performance enhancements Beta

ThoughtSpot’s custom calendar feature includes a configuration option that may significantly improve query performance for large datasets, particularly when working with date-based filters. This solution was specifically built for Google Big Query and Databricks, however, the query pattern may result in improved performance for other platforms with similar query planning engines.

For example, consider a dataset that fulfills the following criteria:

  • Large fact tables that partitioned on date columns

  • Partition pruning requires a direct filter to the applied to the date column, rather than inferred from date columns

  • Queries using custom calendar date attributes (quarters, fiscal years, etc.)

  • Environments where date-based queries are experiencing performance issues

  • This feature is specifically helpful for common time-based queries such as:

    • Sales This Year

    • Sales Last 52 Weeks

    • Sales Last Quarter

Query configuration options

Note that the following are independent options available for custom calendars. They can be used in conjunction.

Separate Common Table Expressions for custom calendar joins

This option moves DATE DIM and Custom Calendar table joins to a separate Common Table Expression (CTE). It uses this CTE in joins against fact tables to enable partition pruning. It prevents optimizer from deferring crucial joins that would otherwise block partition pruning.

This option is applicable when the data model includes a dimensional date table. That is, this CTE pattern will not be invoked if the date column is from the fact table itself.

Approximate date filtering

This feature was developed specifically for Google BigQuery. When enabled, the resulting query generation automatically infers date boundaries based on the search range. Query generation then adds a direct filter to the fact table and dimension tables date columns. This results in a reduction to the initial data scanned.

Note that this is an approximate date filter. The design is to apply a predicate that applies a filter that returns slightly more data than the dynamic period. That is:

  • Last Month will calculate the start date of the Gregorian prior month.

  • Last Week will calculate the start of the prior Gregorian week.

The reason for this design is that weeks, months, years, etc. do not align with Gregorian calendars. This approximate filter ensures that data is partitioned and then the actual filter is applied to ensure that the results are correct.

Benefits

  • Significantly faster query performance on large datasets.

  • Better utilization of database optimization capabilities.

  • Reduced timeout issues with complex date-based filters.

  • Improved overall search experience.

  • Reduction in total bytes scanned.

Technical implementation

The feature is enabled via a cluster-level configuration flag. To enable this feature for your ThoughtSpot implementation, please contact ThoughtSpot Support.

Limitations

  • This feature may not improve all query types (for example, sales august yearly).

  • You cannot modify the automatically added approximate date filters.

  • The feature works for approximate date filters rather than exact.

Related information

Was this page helpful?
×