Time series Functions.

In OBIEE 10g we are having two time series functions
1. Ago( )
2. ToDate( )

1. Ago( )
    This function calculates aggregated value for a measure as of some time period ( a month 
    ago, or a year ago) from the current time. This function is passing three parameters.
        Syntax: Ago(<<Measure>>, <<Level>>, <<Number of Periods>>) 
     i. Measure: A measure column which you to make use in this function 
    ii. Level : On which level you want to calculate this measure. This level is selected from 
        the Time Dimension hierarchy
   iii. Number of Period: A numerical value which will go for how many level 
        you want to go before from current time.
        E.g.: Ago("SH"."SALES"."AMOUNT_SOLD" ,  "SH"."Time Dimension"."Month" , 1) 
        In the above example I have created a Column called 'Sales Month Ago' using Ago() 
        function.

2. ToDate(  )
     This function aggregates a measure from a beginning of a specified time  period to the 
     currently displayed time. We can create a calculated column using this function by 
     following the same procedure how we have created a column using 'Ago' function.
     Syntax: ToDate(<<Measure>>, <<Level>>) 
     For e.g: ToDate("SH"."SALES"."AMOUNT_SOLD" ,  "SH"."Time Dimension"."Year" ) 
     With the above example 'Amount Sold' Column is the measure and 'Year' is the level 
     Which will calculated the measure  from the beginning of the year to a specified time.


In OBIEE11g with the above function there is one more function has been added


3. PeriodRolling( )
    This function allow us to create a aggregated measure across a specified set of query   
    grain period, rather than within a fixed time series grain. The common use of this 
    function is to create a Rolling Average such '10-Week Rolling Average'
              


Syntax: PeriodRolling(<<Measure>>, <<Starting Period Offset>>, <<Ending Period Offset>>)
     Measure: represents the logical measure column from which you want to derive.
     Starting Period Offset: identify the first period used in the rolling aggregation.
     Ending Period Offset: Identify the last period used in the rolling aggregation.
     For e.g: PeriodRolling( "SH"."SALES"."AMOUNT_SOLD", -2, 0)
    With the above example I'm creating three months rolling sum of  column 'Amount Sold'. It 
    includes past two months with the current month.
    The numerical value '-2' in the offset indicates the month Jan-12, Feb-12 if our current   
    month is 'Mar-12' The numerical value '0' in the offset indicates the Current Month
Note:

PeriodRolling( ) function which will not the calculate the Average sum of three months for the measure. Average (AVG( ) function in OBIEE) computes the average of the database rows accessed at the storage grain. So If you want to calculate the 3-Month sales average for the above explained Period Rolling example we have to write the syntax like below:

3-Month Sales Average = 
(PeriodRolling( "SH"."SALES"."AMOUNT_SOLD", -2, 0) )/3