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

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

  • When creating or updating a calendar, a table is created in the external database.

  • Date format must use MM/DD/YYYY. No other formats are supported.

  • Calendars, by default, use English names for days of the week, and month, but can be manually changed by downloading and editing the calendar file.

  • ThoughtSpot doesn’t support using month offset for custom calendars using 4-4-5, 4-5-4, or 5-4-4 configurations. Month offset is a separate calendar type.

  • Maximum scope of the date dimension is 90 years.

  • Maximum length of a calendar year is 12 months.

  • Calendar displays quarter numbers based on the Gregorian calendar (which starts on January 1st). If your custom calendar begins any other date, you must adjust the quarter numbers to align with your calendar. For example: If your custom calendar begins on April 1st, the calendar would incorrectly show April, May and June as quarter 2. In this case, you would need to correct this to indicate those months are quarter 1 and correct the subsequent months to have the correct quarter.

  • Maximum calendar length for connections to specific cloud data warehouses:

    • Google BigQuery: 20 days

    • Microsoft Azure: 750 days

    • Amazon Redshift: 2,000 days

    • Snowflake, and other cloud data warehouses: 20,000 days

      Maximum calendar length is determined by the cloud data warehouse you connect to. To extend the calendar length for these connections, contact ThoughtSpot Support.

  • ThoughtSpot uses ISO week format for the week of [month | quarter | year] keywords, which means that the last few days of a quarter may sometimes show as the first few days of the next quarter, based on the ISO week date system.

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.

Related information