Time Series Functions

Time series functions enable you to aggregate and forecast data based on time dimensions. For example, you might use the AGO function to calculate revenue from one year ago.

Time dimension members must be 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.

Function Example Description Syntax

Function	Example	Description	Syntax
AGO	`SELECT Year_ID, AGO(sales, year, 1)`	Calculates the aggregated value of a measure in a specified time period in the past. For example, to calculate monthly revenue one year ago, use `AGO(Revenue, Year, 1, SHIP_MONTH)`. To calculate quarterly revenues in the last quarter, use `AGO(Revenue, Quarter, 1)`.	`AGO(MEASURE, TIME_LEVEL, OFFSET)` Where: MEASURE represents the measure to calculate, for example, revenue. TIME_LEVEL represents the time interval, which must be Year, Quarter, Month, Week, or Day. OFFSET represents the number of time intervals to calculate back to, for example, 1 for one year.
PERIODROLLING	`SELECT Month_ID, PERIODROLLING (monthly_sales, -1, 1)`	Calculates 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, `PERIODROLLING` can compute sales for a period that starts at a quarter before and ends at a quarter after the current quarter.	`PERIODROLLING(measure, x [,y])` Where: MEASURE represents the name of a measure column. X is an integer that represents the offset from the current time. Y is an integer that represents the number of time units over which the function calculates. HIERARCHY is an optional argument that represents the name of a hierarchy in a time dimension such as YR, MON, DAY, that you want to use to compute the time window.
TODATE	`SELECT Year_ID, Month_ID, TODATE (sales, year)`	Calculates the aggregated value of a measure from the start of a time period to the latest time period, for example, year to date calculations. For example, to calculate Year to Date Sales, use `TODATE(sales, year)`.	`TODATE(MEASURE, TIME_LEVEL)` Where: MEASURE represents an expression that references at least one measure column, for example, sales. TIME_LEVEL represents the time interval, which must be Year, Quarter, Month, Week, or Day.

AGO

SELECT Year_ID, AGO(sales, year, 1)

Calculates the aggregated value of a measure in a specified time period in the past. For example, to calculate monthly revenue one year ago, use AGO(Revenue, Year, 1, SHIP_MONTH). To calculate quarterly revenues in the last quarter, use AGO(Revenue, Quarter, 1).

AGO(MEASURE, TIME_LEVEL, OFFSET)

Where:

MEASURE represents the measure to calculate, for example, revenue.
TIME_LEVEL represents the time interval, which must be Year, Quarter, Month, Week, or Day.
OFFSET represents the number of time intervals to calculate back to, for example, 1 for one year.

PERIODROLLING

SELECT Month_ID, PERIODROLLING (monthly_sales, -1, 1)

Calculates 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, PERIODROLLING can compute sales for a period that starts at a quarter before and ends at a quarter after the current quarter.

PERIODROLLING(measure, x [,y])

Where:

MEASURE represents the name of a measure column.
X is an integer that represents the offset from the current time.
Y is an integer that represents the number of time units over which the function calculates.
HIERARCHY is an optional argument that represents the name of a hierarchy in a time dimension such as YR, MON, DAY, that you want to use to compute the time window.

TODATE

SELECT Year_ID, Month_ID, TODATE (sales, year)

Calculates the aggregated value of a measure from the start of a time period to the latest time period, for example, year to date calculations.

For example, to calculate Year to Date Sales, use TODATE(sales, year).

TODATE(MEASURE, TIME_LEVEL)

Where:

MEASURE represents an expression that references at least one measure column, for example, sales.
TIME_LEVEL represents the time interval, which must be Year, Quarter, Month, Week, or Day.