TS_CUSTOM_CALENDAR_EXPLAINED

1_README.md

ThoughtSpot Custom Calendar

The Custom Calendar feature in ThoughtSpot powers the date keywords. The concepts "yearly", "quarterly", or "weekly" may be skewed (or offset) based on what day determines the start and end boundaries for each of those periods.

This is the ONLY purpose the Custom Calendar feature serves - to provide semantic meaning to DATE and DATE_TIME columns. You cannot use Custom Calendars to encode custom date keywords (eg. Is Holiday = TRUE|FALSE).

When creating a Custom Calendar from a SQL query, the table DDL must exactly match the names and types that ThoughtSpot expects. For this reason, it is advised to create a separate database Table or View in order to isolate these columns.

---

Examples

For example, the default GREGORIAN CALENDAR in ThoughtSpot..

• begins on Januay 1st
• ends on December 31st
• start of week occurs on a Monday

---

Meanwhile, Data Managers (those who have Can Manage Data), can configure alternate calendars in order to change the meaning of this kind of query.

Here is an example FISCAL CALENDAR which..

• begins on August 1st
• ends on July 31st
• start of week occurs on a Monday

---

Once the table object is created in the database..

You will need to let ThoughtSpot know about its existence. This can be done by navigating to Data Workspace > Utilities > Custom calendar.

You may have any number of calendar semantics configured for a Connection, however the specific Calendar object within ThoughtSpot cannot be re-used across Connections. Each Calendar may point to the same external database table however, allowing re-use of the semantic.

When creating a new Calendar, you will need to select Existing Table to reference your external database table.

Important

The Calendar Name field is USER FACING!

Take special care to name this field appropriately for business users, as you would with Column Names.

---

Finally..

apply the calendar type to your Models in ThoughtSpot. This sets the "default calendar semantic" for your DATE and DATE_TIME columns for the Models you applied it to.

If users need to select between multiple calendars, they may type the calendar name in Search and Formulas (see the original screenshots for calendar and fiscal calendar names!)

---

2_TS_CALENDAR_VIEW_STRUCT.sql

/*
 * The CTE below walks through the explanation of each CCAL-required field.
 */

WITH fiscal_calendar AS (
    /*
     * THIS IS THE FISCAL CALENDAR STRUCTURE.
     * - COLUMN NAMES MUST BE IDENTICAL.
     * - COLUMN TYPES MUST BE IDENTICAL WHAT IS DOCUMENTED BELOW.
     */
    SELECT
        /* THIS IS JOINKEY FOR THE DATE SEMANTIC. */
        ('2024-12-27' :: DATE) AS date

        /* THE FOLLOWING COLUMNS DEFINE WHAT `date` MEANS IN THE `fiscal_calendar`.
         *
         * FOR THIS EXAMPLE, WE'LL CONSIDER A FISCAL YEAR WHICH HAS THE FOLLOWING SEMANTICS:
         * - START IN MAY , END IN APRIL
         * - STARTS ON SUNDAY (INSTEAD OF MONDAY)
         * - CALENDAR BEGAN ON 05/01/2000
         * 
         * FISCAL SEMANTIC COLUMNS WILL EXEMPLIFY THE Friday, December 27, 2024 AS THE DATE.
         */

        /* Name of the day in a week. */
        ('Friday' :: VARCHAR) AS day_of_week

        /* Name of the month. */
        ('December' :: VARCHAR) AS month

        /* The Fiscal Quarter number. */
        ('Q3' :: VARCHAR) AS quarter

        /* The Fiscal Year number. */
        (2025 :: VARCHAR) AS year

        /* Day number of the week, 1-indexed. */
        (6 :: NUMBER) AS day_number_of_week

        /* Week number of the month, can exceed 4. */
        (4 :: NUMBER) AS week_number_of_month

        /* Week number of the quarter. */
        (4 :: NUMBER) AS week_number_of_quarter

        /* Week number of the year. */
        (34 :: NUMBER) AS week_number_of_year

        /* Flags a date as a weekend or not. */
        (FALSE :: BOOLEAN) AS is_weekend

        /* Month and Year concatenated. */
        ('December 2025' :: VARCHAR) AS monthly

        /* Quarter and Year concatenated. */
        ('Q3 2025' :: VARCHAR) AS quarterly

        /* Day number of the period, starting from the first date of the MONTHLY column. */
        /* Day number of the period, starting from the first date of the QUARTERLY column. */
        /* Day number of the period, starting from the first date of the YEAR column. */
        ( 27 :: NUMBER) AS day_number_of_month
        ( 27 :: NUMBER) AS day_number_of_quarter
        (240 :: NUMBER) AS day_number_of_year

        /* Month number of the period, starting from the first date of the QUARTERLY column. */
        /* Month number of the period, starting from the first date of the YEAR column. */
        (1 :: NUMBER) AS month_number_of_quarter
        (7 :: NUMBER) AS month_number_of_year

        /* Quarter number of the period, starting from the first date of the YEAR column. */
        (3 :: NUMBER) AS quarter_number_of_year

        /* Number of WEEKS and their BOUNDARIES since the beginning of the CALENDAR. */
        /* NOTE: The END_OF_PERIOD boundary is exclusive. */
        (1286 :: NUMBER) AS absolute_week_number
        ('2024-12-22' :: DATE) AS start_of_week_epoch
        ('2024-12-29' :: DATE) AS end_of_week_epoch

        /* Number of MONTHS and their BOUNDARIES since the beginning of the CALENDAR. */
        /* NOTE: The END_OF_PERIOD boundary is exclusive. */
        (296 :: NUMBER) AS absolute_month_number
        ('2024-12-01' :: DATE) AS start_of_month_epoch
        ('2025-01-01' :: DATE) AS end_of_month_epoch
        
        /* Number of QUARTERS and their BOUNDARIES since the beginning of the CALENDAR. */
        /* NOTE: The END_OF_PERIOD boundary is exclusive. */
        (99 :: NUMBER) AS absolute_quarter_number
        ('2024-12-01' :: DATE) AS start_of_quarter_epoch
        ('2025-03-01' :: DATE) AS end_of_quarter_epoch
        
        /* Number of YEARS and their BOUNDARIES since the beginning of the CALENDAR. */
        /* NOTE: The END_OF_PERIOD boundary is exclusive. */
        (24 :: NUMBER) AS absolute_year_number
        ('2024-05-01' :: DATE) AS start_of_year_epoch
        ('2025-05-01' :: DATE) AS end_of_year_epoch

    FROM
        my_date_table AS d
    
    WHERE
        d.date = '2024-12-27'
)