OBIEE and Time Series Calculations in analysis (Ago‎, Period Rolling‎, To Date‎)

 OBIEE and Time Series Calculations in analysis (Ago‎, Period Rolling‎, To Date‎)

In OBIEE 11 we can use the Time Series Function in analysis and not only in Administration.

2 of the functions require a "time level". So in this post we will learn how to find it and the meaning of each function.
Please note the Time Series Functions are not subject to analysis filters. For example I can create a filter on analysis to show only month X, but if I use the AGO function with last month parameters, it will return data of month X-1, despite the filter. This is the desired behavior of such function.

Time Level:

Unlike the Administration where you can select the time level from the relevant option on the left, in analysis you have to write it yourself. For example, in Sample Sales we have the following Time Dimension:

The Time Level is "Folder Name"."Hierarchy Name"."Level". In our case it's "Time"."Time Hierarchy"."Month".

Time Series Calculations

AGO:

Syntax: AGO(expr, time_level, offset)


Example: AGO("Base Facts"."Revenue","Time"."Time Hierarchy"."Month", 1)

   
A time series aggregation function that calculates the aggregated value from the current time back to a specified time period. In our example AGO provides the Revenue 1 Month Ago from the relevant row data.

For the following example: 

We get this result:

We have the above AGO definition:

AGO("Base Facts"."Revenue","Time"."Time Hierarchy"."Month", 1)

Since the column Month is part of this analysis, we can omit  the time level of the function. The following will give the same result:

AGO("Base Facts"."Revenue", 1)

But it will not work if you delete the Month column from the analysis:

What kind of analysis do I need for Ago without month as a column?

For example when you want the value for this month and last month only as 2 column (so you can compare them). For example:

This criteria (with only 1 month in the filter)

Returns this result:

We need the Time Level here.

Or this example:
I added this column AGO("Base Facts"."Revenue" , "Time"."Time Hierarchy"."Year", 1)
It returns the revenue of a month 1 year ago.

Period Rolling:

Syntax: PERIODROLLING(measure, x [,y])
 Example: PERIODROLLING("Base Facts"."Revenue", -1 ,1 )

This function computes the sum of a measure over the period starting x units of time and ending y units of time from the current time. The unit of time is determined by the measure level of the measures in its first argument and the query level of the query to which the function belongs. In our example the function returns the last, the current and next month aggregated Revenue.

 Few examples:
PERIODROLLING("Base Facts"."Revenue", 0,0) - Returns the current month Revenue (similar to using "base Facts"."Revenue" without the function)
PERIODROLLING("Base Facts"."Revenue", -1,0) - Returns the last month (the -1) and current month Revenue.
PERIODROLLING("Base Facts"."Revenue", -1,1) - Returns the last, the current and next month aggregated Revenue.
PERIODROLLING("Base Facts"."Revenue", -1,2) - Returns the last, the current and next 2 month aggregated Revenue.

TODATE

Syntax: TODATE(expr, time_level)

Example: TODATE("Base Facts"."Revenue", "Time"."Time Hierarchy"."Quarter")

A time series aggregation function that aggregates a measure attribute from the beginning of a specified time period to the current time. In our example we get the total revenue since the beginning of the quarter.

Example:
TODATE("Base Facts"."Revenue", "Time"."Time Hierarchy"."Quarter") returns the revenue from the beginning of the relevant quarter. In our example the result of this column in each first month of quarter is equal to the "regular" Revenue.
TODATE("Base Facts"."Revenue", "Time"."Time Hierarchy"."Year") returns the revenue from the beginning of the relevant Year.

Just as we did with AGO we can run TODATE without Time Dimension columns in the analysis (but we use it in filter). 
For example:

Results in:

You can't use PERIODROLLING this way.