Configure Time Variants

What this covers

A time variant is a derived form of a base measure that answers a time-intelligence question without forcing the modeller to write a separate measure. Examples are year-to-date revenue, prior-quarter order count, and 12-month trailing average. In Tessallite each variant you tick on a base measure becomes its own first-class measure row in the catalog, named <base>_<variant> (for example revenue_ytd). The variant row inherits the base measure's source column, format, data type, default aggregation, and additivity at creation. This article explains which variants exist, when they are admissible, and how to create them.

Before you start — the prerequisite chain

Creating a time variant requires a chain of configuration steps to be completed first. If any step is missing, the variant will appear as "not eligible" with a reason explaining which prerequisite is absent. The full chain is:

1. Define the base measure

The measure you want to extend must already exist. See Define Measures.

2. Create a time hierarchy with a calendar type

A time hierarchy tells the system which time levels (year, quarter, month, week, day) are available, which time calculations are enabled at each level, and what calendar system governs period boundaries.

To create one:

1. Open the Hierarchies panel in the Toolbelt.
2. Create a new hierarchy. Set its dimension kind to time.
3. Set the calendar type to one of:

• standard — Gregorian calendar, year starts January 1.
• fiscal — Gregorian calendar with a shifted year start. Select the fiscal year start month (e.g. April for a UK fiscal year, July for Australian).
• hijri — Islamic calendar, year starts 1 Muharram.
• iso — ISO 8601 week-based year.

1. Add levels for each time granularity you need (e.g. year, quarter, month). On each level, set the time unit and enable the allowed time calculations for the variants you plan to use.

Period boundaries (where a year, quarter, or month starts and ends) are computed automatically from the calendar type. A calendar table is not required — the system derives boundaries using date expressions. If a calendar table IS present in the model, the system uses it for backward compatibility. The system automatically associates measures with the time hierarchy that shares the same table as the measure's source column — no manual linking step is needed.

Prerequisite summary

Available variants

Default n for trailing_n is 12; default n for moving_avg_n is 30. Both can be overridden per measure.

Admission rules

A variant can be ticked on a base measure only when all of the following are true for the measure's associated time hierarchy:

1. The variant's family appears in at least one level's allowed_time_calcs.
2. The variant's required time unit (if any) is present as a level time_unit in the same hierarchy.
3. If the variant is period-aware, the associated hierarchy has a calendar type configured.

Variants that fail any rule are not offered for selection in the drawer.

Creating variants — two paths

There are two ways to create time variants. Both produce the same result — a new measure row in the catalog.

Path A: From the Measures panel (Toolbelt)

1. Open the base measure in the Drawer (Toolbelt → Measures → click the measure name).
2. Under Time variants, tick the variants you want. For trailing_n and moving_avg_n, enter an N (defaults: 12 and 30).
3. For period-boundary variants, select the hierarchy to use. The dropdown shows all hierarchies with matching capabilities. Different hierarchies point to different calendar tables (e.g. standard vs fiscal), so the choice determines which calendar system the variant uses.
4. Click Save. The catalog refreshes; each ticked variant appears as its own measure row beside the base, named <base>_<variant>.

Path B: From the Measure Query Panel (pivot table)

This is a shortcut for when you are actively testing a measure and want to quickly add a variant without leaving the pivot context.

1. Open the Measure Query Panel from the Toolbelt.
2. Select a measure in the Measure dropdown.
3. Next to the measure dropdown, find the variant button — a small icon showing a function symbol (f) with a plus sign (+).
4. Click the button. A popover opens showing all 14 variant kinds.
5. Each variant shows its eligibility status:

• Eligible variants display the suggested name (e.g. base_amount_ytd) and can be clicked to create immediately.
• Ineligible variants show the specific reason they cannot be created and what needs to be configured. For example: "This measure has no associated time hierarchy. Create a time hierarchy with the required levels on the same table as the measure's source column."
• Already added variants are greyed out.

1. For parametric variants (trailing_n, moving_avg_n), a text field appears where you can enter the window size N.
2. Click an eligible variant to create it. The new measure is added to the catalog and becomes available in the pivot table's measure dropdown.

Managing variants

To remove a variant, untick it in the Measures panel Drawer and Save. The variant row is deleted; queries that referenced it will fail with an unknown-measure error.

To rename or re-format a variant row, open the variant directly. Source column, aggregation, and data type are inherited from the base and are not editable on the variant row — change them on the base.

Multiple variants with different calendars

You can create the same variant type multiple times on the same base measure, each with a different hierarchy:

• revenue_ytd — year-to-date using the standard calendar hierarchy
• revenue_ytd_fiscal — year-to-date using the fiscal calendar hierarchy

Each produces a separate measure row with distinct period boundaries. BI tools see them as independent measures and can place them side by side in the same pivot table.

What happens when you delete a hierarchy

If you delete a hierarchy that a variant depends on, the variant loses its period-boundary capability. The base measure still works, but the variant row becomes invalid and is marked with a warning. Either re-create the hierarchy or remove the variant.

How variants are computed

Each variant is rewritten into a window function at query time. Period boundaries are computed using SQL expressions derived from the hierarchy's calendar type — for example, EXTRACT(YEAR FROM date) for a standard calendar or a fiscal-year CASE expression for fiscal calendars. If a calendar table is present in the model, the system uses it instead of expressions for backward compatibility. Postgres is the canonical authoring dialect; for BigQuery and Spark, Tessallite transpiles the canonical SQL via sqlglot. The variant row is metadata only — it does not duplicate the value in storage unless the AI optimiser proposes a per-variant aggregate.

Note: If a variant query is slow, run the AI optimiser. The optimiser scores per-variant aggregates the same way it scores base-measure aggregates and proposes pre-aggregated tables when the cost-benefit is positive. See Use the AI optimiser.

Variants in pre-aggregates (limitation)

The optimiser materialises only window-based variants in CTAS pre-aggregate tables: lag, trailing_n, and moving_avg_n. These are computed inside the aggregate using a window function over the per-grain rows.

Period-aware variants are not materialised. Variants in this list always rewrite over the base measure's pre-aggregate (or the source) at query time:

• prior_year, prior_quarter, prior_month, prior_week
• ytd, qtd, mtd, wtd
• ytd_prior_year, yoy_growth, yoy_growth_pct

These variants depend on a calendar-table JOIN whose validity moves with the calendar contents. Baking that JOIN into a CTAS would couple the aggregate to a specific calendar snapshot, so Tessallite refuses the materialisation by design. They still execute correctly via the rewriter — only the pre-aggregate path is closed.

A window-based variant additionally requires the aggregate's grain to contain a time dimension (used for ORDER BY in the window). If you create an aggregate at a non-time grain, only base measures and non-variant aggregations are materialised.

Troubleshooting

Pitfalls

• Creating a period-boundary variant without a calendar type. The variant will be marked as ineligible. Edit the hierarchy and set a calendar type before enabling period-boundary variants.
• Mixing calendars in the same pivot query. Querying revenue_ytd (standard) and revenue_ytd_fiscal (fiscal) in the same pivot with a single date dimension produces meaningless cross-calendar numbers. Use one calendar system per query context.
• Assuming all variants need a calendar type. Window variants (lag, trailing_n, moving_avg_n) work without any calendar configuration — they only need a date dimension in the query grain.

Related

• Define Measures
• Configure Calendar Table
• Associate Calendar with Dimensions
• Calendar Types
• Use the AI optimiser