Tuesday, 24 December 2013

OBIEE 11g Release – Evaluate Analytical Function – LEAD

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

LEAD Function

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

LEAD - provides access to a row at a given offset after the current position

Calculation Example


This example covers sales figure across time

Selected Items

"Time"."Per Name Month", "Facts Orders"."Total Orders Value"

Sort Order

"Time"."Per Name Month"


"Time"."Per Name Year" = '2009'

Calculation Name

Next Month Orders

Database Calculation


Calculation for Analytics Using Evaluate Function

CAST(EVALUATE('LEAD(%1,1,0) OVER (ORDER BY %2)',"Facts Orders"."Total Orders Value","Time"."Per Name Month") AS INTEGER)


Cast is included to convert the decimal output into integer, more like rounding. The next month orders for 2009/07 is 0 as there is no data for month 2009/08.

Result – Table View/Graph View

I hope you find this information useful.

1 comment:

  1. Pravin,

    How can I apply a filter on the calculated column using evaluate function ?