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

This documentation describes custom calendar for Connections only. For details on custom calendar for ThoughtSpot’s Falcon database, see Set up custom calendars

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

Supported cloud data warehouses

Custom calendar currently supports the following:

  • Amazon Redshift

  • Databricks

  • Google BigQuery

  • Microsoft Azure Synapse

  • Oracle

  • SAP HANA

  • Snowflake

  • Teradata

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.

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.

Prerequisites

  • Before you can create a custom calendar, you must contact ThoughtSpot Support to enable the custom calendar feature on your cluster.

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