Time Navigation

Time navigation allows member formulas to reference values at positions before or after the current member in an ordered time dimension.

Casabase Cube provides two time-navigation functions:

• LAG — navigate backward in time
• LEAD — navigate forward in time

These functions provide relative navigation. Rather than hard-coding a specific period or year, a formula can reference a value relative to the time member currently being evaluated.

For practical examples such as prior-period variance and year-over-year growth, see Common Formula Patterns.

LAG

LAG navigates backward from the current member in a recognized time dimension.

The syntax is:

[DIMENSION].[LAG(TIME_DIMENSION, offset)]

For example:

[PERIOD].[LAG(PERIOD, 1)]

references the member immediately before the current PERIOD member.

Similarly:

[YEARS].[LAG(YEARS, 1)]

references the member immediately before the current YEARS member.

The offset determines how many positions to move backward.

For example:

[YEARS].[LAG(YEARS, 2)]

references the member two positions before the current YEARS member.

LAG Example

Assume YEARS contains the following ordered members:

FY23
FY24
FY25
FY26

If the current member is:

FY26

then:

[YEARS].[LAG(YEARS, 1)]

references:

FY25

and:

[YEARS].[LAG(YEARS, 2)]

references:

FY24

LEAD

LEAD navigates forward from the current member in a recognized time dimension.

The syntax is:

[DIMENSION].[LEAD(TIME_DIMENSION, offset)]

For example:

[PERIOD].[LEAD(PERIOD, 1)]

references the member immediately after the current PERIOD member.

Similarly:

[YEARS].[LEAD(YEARS, 1)]

references the member immediately after the current YEARS member.

LEAD Example

Given:

FY23
FY24
FY25
FY26

if the current member is:

FY24

then:

[YEARS].[LEAD(YEARS, 1)]

references:

FY25

and:

[YEARS].[LEAD(YEARS, 2)]

references:

FY26

Multidimensional Context

Time navigation changes the coordinate of the referenced time dimension while preserving the current coordinates of the other dimensions.

For example, assume a formula is being evaluated at:

ACCOUNT  = Revenue
ENTITY   = North America
SCENARIO = Actual
YEARS    = FY26
PERIOD   = Jan

A reference to:

[YEARS].[LAG(YEARS, 1)]

conceptually evaluates the value at:

ACCOUNT  = Revenue
ENTITY   = North America
SCENARIO = Actual
YEARS    = FY25
PERIOD   = Jan

Only the YEARS coordinate changes.

Similarly:

[PERIOD].[LAG(PERIOD, 1)]

changes only the PERIOD coordinate while retaining the current Account, Entity, Scenario, Year, and other dimensional coordinates.

This allows the same relative time formula to operate across the multidimensional intersections returned by a query.

Combining Time Navigation with Other Coordinates

Time navigation can be combined with cross-dimensional references.

For example:

[PERIOD].[LAG(PERIOD, 1)]
  ->[VIEW].[Periodic]

references the previous PERIOD member at:

VIEW = Periodic

Similarly:

[YEARS].[LAG(YEARS, 1)]
  ->[SCENARIO].[Actual]

changes both coordinates:

YEARS    = Previous Year
SCENARIO = Actual

while preserving the current coordinates of every other dimension.

This allows relative time navigation to participate in the same multidimensional coordinate-shift logic as other member references.

See Formula Syntax for cross-dimensional reference syntax.

Time Dimension Recognition

LAG and LEAD can only be used with dimensions that Casabase Cube recognizes as time dimensions.

In a typical multidimensional cube, PERIOD is the hierarchy explicitly configured as the cube’s Time hierarchy. However, time navigation is not limited to that hierarchy.

Casabase Cube can also recognize other dimensions that represent ordered time sequences, such as a separate YEARS dimension.

A dimension is eligible for time navigation when it is recognized through one of the following mechanisms:

• It is the cube’s declared Time dimension.
• It participates in Dynamic Time Series configuration.
• It uses a recognized time dimension name.
• It is explicitly configured as a navigation time dimension through NAV_TIME_DIMS.

Standard time dimension names recognized for navigation include:

PERIOD
YEARS
MONTH
QUARTER
WEEK
DAY

This allows a cube to use navigation such as:

[PERIOD].[LAG(PERIOD, 1)]

for previous-period navigation and:

[YEARS].[LAG(YEARS, 1)]

for previous-year navigation, even when PERIOD is the hierarchy explicitly designated as Time.

Time navigation is therefore based on whether Casabase Cube recognizes the dimension as representing an ordered time sequence, not solely on whether the dimension carries the cube’s Time designation.

Non-Time Dimensions

LAG and LEAD are not general-purpose previous-member and next-member functions.

For example, they should not normally be used to navigate dimensions such as:

ACCOUNT
ENTITY
PRODUCT
GEOGRAPHY

Although these dimensions have a hierarchy and member order, that order does not inherently represent chronological sequence.

For example:

[ENTITY].[LAG(ENTITY, 1)]

should not be interpreted as “the previous Entity.”

The position of an Entity member in a hierarchy is a structural property, not a time relationship.

If LAG or LEAD is used against a dimension that Casabase Cube does not recognize for time navigation, the query is refused rather than treating ordinary hierarchy order as chronological order.

Custom Time Dimension Names

Some cubes use time dimension names that differ from the standard names recognized automatically by Casabase Cube.

For example:

FISCAL_YEAR

might be used instead of:

YEARS

The NAV_TIME_DIMS cube variable can explicitly identify additional dimensions that should be eligible for LAG and LEAD navigation.

For example:

CALL CUBE.MANAGE_CUBE_VAR(
    'MYCUBE',
    'create',
    'NAV_TIME_DIMS',
    'FISCAL_YEAR',
    TRUE,
    NULL
);

NAV_TIME_DIMS can contain a comma-separated list when more than one additional time dimension must be recognized.

Once admitted as a navigation time dimension, the custom dimension can participate in relative references such as:

[FISCAL_YEAR].[LAG(FISCAL_YEAR, 1)]

The configured dimension should represent a genuine ordered time sequence.

NAV_TIME_DIMS should not be used simply to enable previous-member or next-member navigation on ordinary business dimensions.

Time Member Ordering

LAG and LEAD navigate according to the time dimension’s member sort order.

For example:

FY23 ──► FY24 ──► FY25 ──► FY26

when the current member is FY26:

LAG(YEARS, 1) = FY25
LAG(YEARS, 2) = FY24
LAG(YEARS, 3) = FY23

Similarly:

Jan ──► Feb ──► Mar ──► Apr

when the current member is Feb:

LEAD(PERIOD, 1) = Mar
LEAD(PERIOD, 2) = Apr

The ordering of members in a time dimension is therefore significant.

LAG and LEAD do not determine chronology from the text of member names. They navigate the sequence defined by the time dimension’s member sort order.

Boundary Conditions

A navigation request can reach beyond the available members in the time sequence.

For example, given:

FY23
FY24
FY25
FY26

FY23 has no previous year in the available sequence, while FY26 has no subsequent year.

Formulas using relative navigation should therefore be tested at the boundaries of the available time range.

This is particularly important when using:

• Multi-position offsets
• Prior-period or prior-year comparisons
• Forward-looking references

A formula may behave correctly for most time members while encountering a missing reference at the beginning or end of the available sequence.

Time Navigation vs. Dynamic Time Series

LAG and LEAD perform relative time navigation.

They should not be used to manually reproduce cumulative time calculations.

The distinction is:

Requirement	Capability
Previous period	LAG
Previous year	LAG
Next period	LEAD
Next year	LEAD
Period-over-period comparison	LAG
Year-over-year comparison	LAG
Year-to-date	Dynamic Time Series
Quarter-to-date	Dynamic Time Series
Month-to-date	Dynamic Time Series

Relative Navigation

A calculation that requires a value from another relative point in time uses LAG or LEAD.

Conceptually:

Current Position
       │
       ├── LAG ──► Earlier Position
       │
       └── LEAD ─► Later Position

Time Accumulation

A calculation such as:

Jan + Feb + Mar + ... + Current Period

requires accumulation across a range of time members.

This is a Dynamic Time Series calculation.

Dynamic Time Series should be used for YTD, QTD, MTD, and similar cumulative calculations rather than recreating those calculations with member formulas.

Dynamic Time Series can also account for time-specific behavior such as time-balance account types, period boundaries, and skip settings.

Time-balance types used with this model are FLOW, FIRST, LAST, and AVERAGE, with skip settings NONE, MISSING, ZEROS, and MISSING_AND_ZEROS.

Choosing the Right Time Capability

A useful distinction is:

Need a value at another
relative point in time?
          │
          ▼
      LAG / LEAD


Need an accumulated value
through a time range?
          │
          ▼
 Dynamic Time Series

Use LAG or LEAD when the calculation requires a value at another relative time coordinate.

Use Dynamic Time Series when the calculation requires an accumulated value across multiple periods.

For practical formula examples using relative time navigation, see Common Formula Patterns.

Key Takeaways

• LAG navigates backward in a recognized time dimension.
• LEAD navigates forward in a recognized time dimension.
• The offset specifies how many positions to navigate.
• Time navigation changes the referenced time coordinate while preserving the remaining multidimensional context.
• LAG and LEAD navigate according to the time dimension’s member sort order.
• PERIOD is typically the hierarchy explicitly designated as Time, but other time dimensions such as YEARS can also be recognized for navigation.
• NAV_TIME_DIMS can explicitly admit additional time dimensions with non-standard names.
• LAG and LEAD are not general-purpose previous-member or next-member functions for arbitrary dimensions.
• Formulas should account for boundaries where a previous or subsequent time member may not exist.
• Use LAG and LEAD for relative time navigation.
• Use Dynamic Time Series for YTD, QTD, MTD, and other cumulative time calculations.

Related Topics

• Formula Syntax
• Common Formula Patterns
• Formula Validation
• Best Practices