Create a custom calendar

Before you begin

You must have administrator-level permissions to create a table in the database of the cloud data warehouse of the connection.

Methods of creating a custom calendar

Use one of the following methods to create a custom calendar:

  • Create a calendar

  • Upload a file

  • Use an existing table

Creating a custom calendar

You can create one of the following calendar types:

  • Month offset

  • Four Four Five

  • Four Five Four

  • Five Four Four

If you need to create a different type of calendar, including one that uses a different language for the names of months or days of the week, you must upload your custom calendar file. In this case, ThoughtSpot suggests you first create a calendar in one of the supported calendar types to use as a template, by following these steps. After that, you can download and update it to your specific requirements.

To create a custom calendar, do the following:

  1. Select Data in the top navigation bar.

    The Data workspace appears.

  2. Click Utilities in the side navigation bar, and select Add/modify custom calendar.

    The Custom calendar page appears.

  3. Select Create Custom Calendar.

    The Create Custom Calendar dialog appears.

    Create custom calendar dialog box

  4. For Calendar Name, enter a name for your calendar.

    The calendar name must be unique in the cluster.

  5. Open the Choose Connection menu, and select the connection where you want to use your custom calendar.

  6. For Database, open the Choose a database menu, and select the database you want to use with your custom calendar.

  7. For Schema, select the Choose a schema menu, and choose the schema you want to use with your custom calendar.

  8. Make sure Create is selected.

  9. For Table, enter a name for the table that will be created for your custom calendar.

  10. For Start Date, enter the date you want your custom calendar to start.

    Both Start Date and End Date must be in MM/DD/YYYY format.

  11. For End Date, enter the date you want your custom calendar to end.

  12. For Calendar Type, select the Choose a calendar type menu, and choose the type of calendar you want to create. Possible types include:

    • MONTH_OFFSET

    • FOUR_FOUR_FIVE

    • FOUR_FIVE_FOUR

    • FIVE_FOUR_FOUR

  13. For Monthly offset, open the menu and select a month.

  14. For Start day of week, open the menu and select a day.

  15. For Quarter name prefix, enter the prefix you’d like to use for quarter numbers.

    Example: Q

  16. For Year name prefix, enter the prefix you’d like to use for years.

    Example: FY

  17. Select Create.

    The Custom calendar page appears again, with the message "Custom calendar created successfully!"

    Your custom calendar appears in the list of calendars.

Uploading a file for a custom calendar

This option provides you the ability to upload a calendar file. You can update an existing calendar by downloading it, making changes to it, and then uploading it.

To upload a file to use as a custom calendar, do the following:

  1. Select Data in the top navigation bar.

    The Data workspace appears.

  2. Click Utilities in the side navigation bar, and select Add/modify custom calendar.

    The Custom calendar page appears.

  3. Select Create Custom Calendar.

    The Create Custom Calendar dialog appears.

    Create custom calendar dialog box

  4. For Calendar Name, enter a name for your calendar.

    The calendar name must be unique in the cluster.

  5. Open the Choose Connection menu, and select the connection where you want to use your custom calendar.

  6. For Database, open the Choose a database menu, and select the database you want to use with your custom calendar.

  7. For Schema, open the Choose a schema menu, and select the schema you want to use with your custom calendar.

  8. Select Upload File.

    Under choose a method

  9. For Table, enter a name for the table that will be created for your custom calendar.

  10. For File, select the Upload button, choose the file, and select Open.

  11. For Separator, select delimiter format used in your calendar file.

Supported delimiters include: Comma(,), Pipe(|), Semicolon(;), and Tab.

  1. Select Create.

    The Custom calendar page appears again, with the message "Custom calendar created successfully!"

    Your custom calendar appears in the list of calendars.

Use an existing table for a custom calendar

This option provides you the ability to use an external table in your connection as a calendar.

To use an external table as a custom calendar, do the following:

  1. Select Data in the top navigation bar.

    The Data workspace appears.

  2. Click Utilities in the side navigation bar, and select Add/modify custom calendar.

    The Custom calendar page appears.

  3. Select Create Custom Calendar.

    The Create Custom Calendar dialog appears.

    Create custom calendar dialog box

  4. For Calendar Name, enter a name for your calendar.

    The calendar name must be unique in the cluster.

  5. Open the Choose Connection menu, and select the connection where you want to use your custom calendar.

  6. For Database, open the Choose a database menu, and select the database you want to use with your custom calendar.

  7. For Schema, open the Choose a schema menu, and select the schema you want to use with your custom calendar.

  8. Select Existing Table.

    Under choose a method

  9. For Table, select an external table to use for creating your custom calendar.

  10. Select Create.

    The Custom calendar page appears again, with the message "Custom calendar created successfully!"

    Your custom calendar appears in the list of calendars.

Formatting the calendar table

The following DDL shows the custom calendar table creation for Snowflake. Other data warehouses will have a similar structure. The name of the table isn’t important, you will specify the name to ThoughtSpot. The columns and type are important, however.

create or replace TABLE FISCAL_CAL(
  "date" DATE,
  "day_of_week" VARCHAR(),
  "month" VARCHAR(),
  "quarter" VARCHAR(),
  "year" VARCHAR(),
  "day_number_of_week" BIGINT,
  "week_number_of_month" BIGINT,
  "week_number_of_quarter" BIGINT,
  "week_number_of_year" BIGINT,
  "is_weekend" BOOLEAN,
  "monthly" VARCHAR(),
  "quarterly" VARCHAR(),
  "day_number_of_month" BIGINT,
  "day_number_of_quarter" BIGINT,
  "day_number_of_year" BIGINT,
  "month_number_of_quarter" BIGINT,
  "month_number_of_year" BIGINT,
  "quarter_number_of_year" BIGINT,
  "absolute_week_number" BIGINT,
  "start_of_week_epoch" DATE,
  "end_of_week_epoch" DATE,
  "absolute_month_number" BIGINT,
  "start_of_month_epoch" DATE,
  "end_of_month_epoch" DATE,
  "absolute_quarter_number" BIGINT,
  "start_of_quarter_epoch" DATE,
  "end_of_quarter_epoch" DATE,
  "absolute_year_number" BIGINT,
  "start_of_year_epoch" DATE,
  "end_of_year_epoch" DATE,
 PRIMARY KEY ("date" )
);

Populating the calendar

Once the table has been created, you need to populate the table with the appropriate data. The date (first column) is the actual calendar date that will be used to map to the custom date values. The following example shows a few records. In this case, the day and month names have been changed to Spanish, the first month is June, and the first day of the week is Monday.

2021-06-01,martes,junio,QTR1,YR2021,2,1,1,1,false,junio YR2021,QTR1 YR2021,1,1,1,1,1,1,1,2021-06-01,2021-06-08,1,2021-06-01,2021-07-01,1,2021-06-01,2021-09-01,1,2021-06-01,2022-06-01

2022-05-28,sábado,mayo,QTR4,YR2021,6,5,13,52,true,mayo YR2021,QTR4 YR2021,28,89,362,3,12,4,52,2022-05-23,2022-05-30,12,2022-05-01,2022-06-01,4,2022-03-01,2022-06-01,1,2021-06-01,2022-06-01

Using your custom calendar in your connection

After you create your custom calendar, you must specify where to use it in your connection.

To use your custom calendar, do the following:

  1. Sign in to your ThoughtSpot cluster and select Data > Connections.

  2. Select the name of the connection where you want to use your calendar.

  3. Select the name of the table where you want to use your calendar.

  4. Find the column where you want to use your calendar.

    The column must use the DATE or DATE_TIME data type.

  5. Refresh the page to make sure your custom calendar is available.

  6. Scroll horizontally to the CALENDAR TYPE column.

  7. In the CALENDAR TYPE column for the column(s) you chose, double-click the existing calendar name (example: "None"), and then select your custom calendar.

  8. Select Save Changes.

    Now, date-related searches in the selected table use your custom calendar.

    After creating a custom calendar, you can easily look at it to confirm it is set up the way you want. For details, see View a custom calendar.

View a custom calendar

After creating a custom calendar, you can easily open it from the ThoughtSpot UI to look at its configuration.

To view a 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 in the side navigation bar, and select Add/modify custom calendar.

    The Custom calendar page appears.

  4. Select the name of the custom calendar you want to view.

    Your custom calendar opens in a window displaying all columns and rows. You can scroll through these to verify your calendar is set up the way you want.

  5. When you’re done viewing your calendar, close the window by selecting Done.

Related information

Was this page helpful?
×