Customer Period Configuration

If using non-standard Gregorian time periods, such as 4-week "months" or a fiscal calendar, it's crucial to ensure your data is structured and interpreted correctly. This document provides detailed guidelines on how to configure your dataset and metadata to teach Max about these unique time periods. By following these instructions, you can seamlessly integrate custom calendar formats, ensuring accurate data filtering, trend analysis, and reporting aligned with your specific time structures.

Data Structure Requirements

To ensure consistency and ease of data manipulation, adhere to the following requirements for your data structure:

Date Units and Sequences

  • Distinct Column for Each Date Unit: Each date unit (e.g., year, month, quarter) must have its own column.
  • Sequence Column for Each Date Unit: Accompany each date unit column with a sequence column, named using the convention date unit + "_sequence". For example, if there is a year column, there must be a year_sequence column.

Example Columns

  1. year
  2. year_sequence
  3. quarter
  4. quarter_sequence
  5. month
  6. month_sequence

Time Extract Component

The time_extract component uses several elements to filter and manipulate date data:

  • span: This is the date filter, represented as a list with three values (start date, end date, and date column). An empty string for the start or end date implies the oldest or latest date, respectively.
  • date column: Specifies the date unit from the data (e.g., year, quarter, month).
  • period_start: The start date value in the same format as the data.
  • period_end: The end date value in the same format as the data.
  • step: Defines the time granularity of the trend, which can be any date column from the data.
  • flags: Special date concepts such as YTD (Year-To-Date) and MAT (Moving Annual Total). If flags are provided without a span key, it defaults to the latest period. If span is specified, it constructs a filter representative of YTD or MAT ending on the specified end date.

Metadata Requirements for Datasets

To facilitate the use of custom calendars and date formats, include the following metadata:

Miscellaneous Information

  • uses_period_calendar: A flag in the miscellaneous information section to enable the custom calendar mode. If set to true, the date unit and sequence columns will be utilized.

Date Setup Key

The date_setup key should include the following subkeys:

  • format_examples (optional): Overrides the auto-generated format information based on the data.
  • span_examples (mandatory): Provides example date ranges for each date dimension in the format [dim_name, start_period, end_period]. This helps the system parse the start and end periods from user queries.
  • extract_examples (mandatory): Contains question/answer pairs with examples of relative dates, detailing how to extract information based on spans, steps, and flags.

Example JSON for Metadata

{
  "uses_period_calendar": true,
  "date_setup": {
    "format_examples": {
      "month": "DEC 2021",
      "quarter": "Q1 2017",
      "year": "1996"
    },
    "span_examples": {
      "year": ["year", "1969", "1971"],
      "quarter": ["quarter", "Q1 2019", "Q3 2021"],
      "month": ["month", "MAR 1912", "JUL 1921"]
    },
    "extract_examples": [
      {
        "Q": "what was the growth of unit count YTD by {month} in France?",
        "A": {"step": "{month}", "flags": "YTD"}
      },
      {
        "Q": "how many units were sold in Q3 {last year} by {month}?",
        "A": {"span": ["quarter", "Q3 2023", "Q3 2023"], "step": "month"}
      },
      {
        "Q": "Sales growth breakout by brand for the last 6 months ending Dec 2023",
        "A": {"span": ["month", "JUL 2023", "DEC 2023"]}
      },
      {
        "Q": "Sales by month Oct - Nov 2023",
        "A": {"span": ["month", "OCT 2023", "NOV 2023"], "step": "month"}
      }
    ]
  }
}

Template Skill Requirements

No changes are required for template skill parameters.

Setting Up Default Periods

Use JSON representation to set up default periods. You can leave the start date or end date empty to set defaults from the oldest available year to 2023 or from 2023 to the latest year, respectively. You can also use flags without span to set the latest YTD or MAT as default filters. Ensure the JSON string is valid (use double quotes inside curly braces) and wrap it with single quotes.

Example JSON Strings

'{"span": ["year", "2023", "2023"], "step": "month"}'
'{"flags": "YTD", "step": "month"}'

Updated

Was this article helpful?