Time Series functions in OBIEE

  • Ago () calculates aggregated value for a measure as of time period ( a month ago, or a year ago) from the current time.
Syntax: AGO(expr, time_level, offset)
Example: AGO("Base Facts"."Revenue","Time"."Time Hierarchy"."Month", 1)
  • ToDate () aggregates a measure from a beginning of a specified time period to the currently displayed time.
Syntax: TODATE(expr, time_level)
Example: TODATE("Base Facts"."Revenue", "Time"."Time Hierarchy"."Quarter")
  • PeriodRolling () allows 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, x [,y])
Example: PERIODROLLING("Base Facts"."Revenue", -1 ,1 )

Above example is 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: 3-Month Sales Average = (PeriodRolling( “SH”.”SALES”.”AMOUNT_SOLD”, -2, 0) )/3