Time Series Functions

Time series functions operate on time-oriented dimensions.
note: The time series functions calculate AGO, TODATE, and PERIODROLLING functions
based on user supplied calendar tables, not on standard SQL date manipulation
functions.
To use time series functions on a particular dimension, you have to designate the
dimension as a Time dimension and set one or more keys at one or more levels as
chronological keys.

Functions

AGO

This function is a time series aggregation function that calculates the aggregated
value from the current time back to a specified time period. For example, AGO
can produce sales for every month of the current quarter and the corresponding
quarter-ago sales.
Time series functions operate on members of time dimensions which are at or
below the level of the function. Because of this, one or more columns that
uniquely identify members at or below the given level must be projected in the
query. Alternatively, you can apply a filter to the query that specifies a single
member at or below the given level.
Multiple AGO functions can be nested if all the AGO functions have the same
level argument. You can nest exactly one TODATE and multiple AGO functions if
they each have the same level argument.

Syntax

AGO(expr, [time_level], offset)

Where

expr is an expression that references at least one measure column.
time_level is an optional argument that specifies the type of time period, such
as quarter, month, or year.
offset is an integer literal that represents the time shift amount.

Example

AGO(sales, year, 1)

It is recommended that you explicitly specify the level of the AGO function using
the time_level argument. If you do not explicitly specify the time_level argument,
the default level is determined as follows:

• If the measure used in the expression is a level-based measure in the time
dimension, then that same level is considered the default AGO level.
• Otherwise, the grain of the measure used in the expression, as determined by
the BY clause of the measure shown in the logical request, is the default Ago
level.

For example, the result of the query:

AGO(sales, 1)

is the same as:

AGO(sales, year_level, 1)

 

 

PERIODROLLING

This function computes the aggregate of a measure over the period starting x
units of time and ending y units of time from the current time. For example, you
can use PERIODROLLING to compute sales for a period that starts at a certain
quarter before and ends at a certain quarter after the current quarter.
Time series functions operate on members of time dimensions which are at or
below the level of the function. Because of this, one or more columns that
uniquely identify members at or below the given level must be projected in the
query. Alternatively, you can apply a filter to the query that specifies a single
member at or below the given level.

note: You cannot nest AGO and TODATE functions within a PERIODROLLING function.
Also, you cannot nest PERIODROLLING, FIRST, FIRST_PERIOD, LAST, and
LAST_PERIOD functions.

If you embed other aggregate functions (like RANK, TOPN, PERCENTILE, FILTER, or
RSUM) inside PERIODROLLING, the PERIODROLLING function is pushed inward. For
example, PERIODROLLING(TOPN(measure)) is executed as TOPN(PERIODROLLING(measure)).

Syntax

PERIODROLLING(measure, x, y [,hierarchy])

Where

measure is the name of a measure column.

x is an integer that specifies the offset from the current time. Precede the integer
with a minus sign (-) to indicate an offset into the past.

y specifies the number of time units over which the function will compute. To
specify the current time, enter 0.

hierarchy is an optional argument that specifies the name of a hierarchy in a
time dimension, such as yr, mon, day, that you want to use to compute the
time window. This option is useful when there are multiple hierarchies in a time
dimension, or when you want to distinguish between multiple time dimensions.numExpr
is any expression that evaluates to a numerical value.

If you want to roll back or forward the maximum possible amount, use the keyword
UNBOUND. For example, the function PERIODROLLING (measure, -UNBOUND, 0)
sums over the period starting from the beginning of time until now.

You can combine PERIODROLLING and AGGREGATE AT functions to specify the
level of the PERIODROLLING function explicitly. For example, if the query level is
day but you want to find the sum of the previous and current months, use the
following:
PERIODROLLING(AGGREGATE(sales AT month), -1)

Examples

PERIODROLLING(monthly_sales, -1, 1)
PERIODROLLING(monthly_sales, -UNBOUND, 2)
PERIODROLLING(monthly_sales, -UNBOUND, UNBOUND)

 

 

Determining the Level Used by the PERIODROLLING Function

The unit of time (offset) used in the PERIODROLLING function is called the level of the
function. This value is determined by the measure level of the measures in its first
argument and the query level of the query to which the function belongs. If a
measure level has been set for the measure used in the function, the measure
level is used as the level of the function. The measure level is also called the storage
grain of the function.
If a measure level has not been set, then the query level is used. The query level is
also called the query grain of the function. When there are multiple hierarchies in
the time dimension, you must specify the hierarchy argument in the PERIODROLLING
function.

 

 

TODATE

This function is a time series aggregation function that aggregates a measure from
the beginning of a specified time period to the currently displayed time. For example,
this function can calculate year-to-date sales.
Time series functions operate on members of time dimensions which are at or
below the level specified in the function. Because of this, one or more columns
that uniquely identify members at or below the given level must be projected in
the query. Alternatively, you can apply a filter to the query that specifies a single
member at or below the given level.
note: A TODATE function may not be nested within another TODATE function. You
can nest exactly one TODATE and multiple AGO functions if they each have the
same level argument.
TODATE is different from the TO_DATE SQL function supported by some databases.
Do not use TO_DATE to change to a DATE data type. Instead, use the CAST function.
See “CAST” for more information.

Syntax

TODATE(expr, time_level)

Where

expr is an expression that references at least one measure column.
time_level is the type of time period, such as quarter, month, or year.

Example

TODATE(sales, year)