OBIEE 11g Release 11.1.1.7 – Evaluate Analytical Function – LAG

As a part of this analytical function series, I would like to explain the usage of analytical function with calculation examples.

LAG Function

LAG function is typically used to compare values in different time periods. For example, compare sales figures in 2000 with sales figures in 2001.

LAG - provides access to more than one row of a table at the same time without a self-join

Calculation Example

Description	This example covers sales figure across time
Selected Items	"Time"."Per Name Month", "Facts Orders"."Total Orders Value"
Sort Order	"Time"."Per Name Month"
Condition	"Time"."Per Name Year" = '2009'
Calculation Name	Previous Month Orders
Database Calculation	LAG(Sales SUM,1) OVER(ORDER BY Year)
Calculation for Analytics Using Evaluate Function	CAST(EVALUATE('LAG(%1,1,0) OVER (ORDER BY %2)',"Facts Orders"."Total Orders Value","Time"."Per Name Month") AS INTEGER)
Notes	Cast is included to convert the decimal output into integer, more like rounding. The previous month orders for 2009/05 is 0 as there is no data for month 2009/04.
Result – Table View/Graph View

I hope you find this information useful.