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.

Related information

Was this page helpful?
×