About Time Series Conversion Functions

Time series functions operate on time-oriented dimensions. To use these 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. This identifies the dimension as having a monotonically increasing value in time (corresponds to chronological order).

NOTE:  It is required that you define a chronological key at a level that can be used to answer your time series query. It is recommended that you define additional chronological keys at other relevant levels for performance reasons.

Currently, AGO and TODATE are the types of time series conversion functions. Currently, you may only enter AGO and TODATE functions in the Expression Builder in the Administration Tool. You cannot use them in coded SQL.

The Ago and ToDate functions allow you use Expression Builder to call a logical function to perform time series calculations instead of aliasing physical tables and modeling logically. The time series functions calculate Period Ago and Period to Date functions based on user supplied calendar tables, not on standard SQL date manipulation functions.

The following list describes the important grains in navigating a time query, using the following query example:

Select quarter, YearAgoSales:

Query grain. The grain of the request. In the query example, the query grain is Quarter.

Time Series grain. The grain at which the aggregation is requested. In the query example, the Time Series grain is Year.

NOTE:  Time series query is valid only if the time series grain is at the query grain or higher.

Storage grain. The query in the example can be computed from daily sales or from monthly sales, or from quarterly sales. The grain of the aggregate source is called aggregation grain.

NOTE:  The chronological key has to be defined at this level.

Ago

A time series aggregation function for relational data sources only. 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. Multiple Ago functions can be nested if all the Ago functions have the same level argument.

NOTE:  You can nest exactly one ToDate and multiple Ago functions if they each have the same level argument.

Syntax:

AGO(, .., )

In that example, is an expression that contains at least one measure, is a model identifier, is a dimension identifier, is a level identifier, and is an integer literal. The following is an example of this syntax:

AGO(model.sales.revenue + 5, model.time.month, 3)

ToDate

A time series aggregation function for relational data sources only. ToDate aggregates a measure attribute from the beginning of a specified time period to the currently displayed time. For example, this function can calculate Year to Date sales.

If unsupported metrics are requested, NULL values will be returned and a warning entry will be written to the NQQuery.log file when the logging level equals three or above. A ToDate function may not be nested within another ToDate function.

NOTE:  You can nest exactly one ToDate and multiple Ago functions if they each have the same level argument.

Syntax:

TODATE(, ..)