In a semantic model, the PERIODROLLING function lets you perform an aggregation across a specified set of query grain periods, rather than within a fixed time series grain.

The most common use is to create rolling averages such as a 13-week Rolling Average.

The PERIODROLLING function doesn't have a time series grain, the length of the rolling sequence is determined by the query grain. For example, the Dollars 3-Period Rolling Average calculates the mean of values from the last 3 months if the query grain is Month, but calculates the mean of the last 3 years if the query grain is Year.

The image shows a report with these two measures.

In the example above , the Dollars 3-Period Rolling Sum and Dollars 3-Period Rolling Avg measures are derived from the Dollars measure.

In Expression Editor, the PERIODROLLING function has the following format:

PeriodRolling(<<Measure>>, <<Starting Period Offset>>, <<Ending Period Offset>>)

<<Measure>> represents the logical measure column from which you want to derive. To create the measure Dollars 3-Period Rolling Sum, you select the measure, Dollars from your existing logical fact tables.

<<Starting Period Offset>> and <<Ending Period Offset>> identify the first period and last period used in the rolling aggregation. The integer is the relative number of periods from the displayed period. In this example, the query grain is month, and the 3-month rolling sum starts 2 periods in the past and includes the current period, that is, for month 2008/07, the rolling sum includes 2008/05, 2008/06 and 2008/07. To create the measure, Dollars 3-Period Rolling Sum, the integers to indicate these offsets are -2 and 0.

Using this function format, you can create the following expression for the measure:

PeriodRolling("Sales"."Base Measures"."Dollars" , -2, 0)

The example also shows a 3-month rolling average. To compute this measure, you can divide the rolling sum that you previously created by 3 to get a 3-period rolling average. The assumption to divide the rolling sum by 3 results from the <<Starting Period Offset>> and <<Ending Period Offset>> fields for the rolling sum that are -2 and 0.

The expression for the 3-month rolling average is:

PeriodRolling("Sales"."Base Measures"."Dollars" , -2, 0) /3

Don't use the AVG function to create a rolling average. The AVG function computes the average of the database rows accessed at the storage grain. To perform the rolling average, you need an average where the denominator is the number of rolling periods at the query grain.

The PERIODROLLING function includes a fourth optional hierarchy argument that lets you specify 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.

See Logical SQL Reference Guide for Oracle Business Intelligence Enterprise Edition.