Time Series Functions in OBIEE

1) Ago

Ago is a time series function that calculates the aggregated value from the current time back to a specified time period. this is useful for comparison  such as amount compared to amount a month ago.

Syntax:  AGO(<Measure >,<Level>,<number of period>)

Measure: A measure column.

Level: On which level you want to calculate this measure such as Year,Quarter,Month etc.

Number of period: how many level you want to go back from current time such as 2 year back,2 months back etc.

Example : AGO(Amount,Year,1) this will give the last year amount .

2) ToDate 

To Date is a time series function that calculates a measure attributes from the beginning of a specified time period to the currently displayed time period.

Syntax : ToDate(<Measure>,<Level>)

Measure: A measure column.

Level: On which level you want to calculate this measure such as Year,Quarter,Month etc.

Example : ToDate(Amount,Year) this will give the amount from the beginning of the year to Currently displayed time.

3) Period Rolling

Period Rolling is a new time series function in 11g .It computes the sum of a measure over the period starting x units of time and ending y units of time from the current time .This function allow us to create a aggregated measures across a specified set of query grain period rather than within a fixed time series grain.

Syntax: PeriodRolling(<Measure>,<Starting Period Offset>, < Starting Period Offset>)

Measure: A measure column.

Starting Period Offset: Starting period used in rolling aggregation.

Ending Period Offset: Ending period used in rolling aggregation.

Example : PeriodRolling(Amount,-2,0) this will give three months rolling sum of column Amount.It includes past two months with the current month. Numerical value -2 indicates May-14,Jun-14 if current month is Jul-14.Numerical value 0 indicates current month.

Note : This function has no time series grain ,the length of the rolling sequence is determined by query grain for example it will give the last three Month amount if the query grain is month , if the query grain is year then it will give last three Year Amount.