Creating a Custom Calendar

The custom calendar table can be built into a cBase, or can remain a text file and be built as part of the calendar declaration. The calendar text file allows you to define custom period type columns that map any given date to user-defined periods. The source text file needs to include one row for each date where a period changes value.

The text file of dates for your custom calendar must have the following columns:

  • Date (YYYY/MM/DD)
  • Year (YYYY)

Optional columns you can include:

  • Half
  • Quarter
  • Tertile
  • Month
  • Week

The Date string is built into the calendar as data type date, while the Year string and optional columns are data type integer. A year period type is always generated, and then a period type is generated for each other integer column. For example, a calendar table with columns Date, Year, Quarter, and Month generates period types year, year-quarter, and year-month.

  1. Plan out the dates and period boundaries for your calendar. A spreadsheet is an easy way to organize your data, because it can be easily sorted and saved to a flat text file. The text file requires a minimum of two columns: Date and Year. For more periods, you can include columns for Half, Quarter, Tertile, Month, or Week. Keep in mind that you will need to add a row for the start date of each period.

    TIP: When defining multiple periods, each column should have data, even if it repeats.

  2. Save your custom calendar data to a tab-delimited text file. Move the file to your Workbench project.

    Example of a partial text file with one optional period:

    Sample Calendar Text File

  3. Right-click the text file, and choose Generate Spectre Build Script.

  4. In the Save As dialog box, choose a location for the Build script (/build recommended). Change the file name, if you want, and click Save.

    Workbench generates a build file and opens the file in the Build Script Editor.

    Custom Calendar Build

  5. Make adjustments as required by selecting the objects to review the Properties Panels:
    • Ensure the Type is correct for each column. (Spectre makes a best guess while creating the Build file.) Date column Type must be set to date and any optional columns to integer.
    • Set the output location.

  6. Select Run > Run or click the Run button, which is the green circle with white arrow, from the toolbar.

    You now have a custom calendar in a cBase that you can use for a period column in other Build scripts or in cPlans.

NOTE:

Consider the following points when constructing calendar data sets:

  • The data set should have one row per period start date.

  • The date entry on each row must be unique.

  • The data set should be sorted down on the date column before the build. Either the text file itself should be generated sorted, or a sort operation should be specified in the Build file.
  • The data set must have a row for every period you expect to use in the data, so you might want to generate some number of historical periods, and some future periods as well, and plan to regenerate the calendar periodically.

See also: