Difference between adjacent values in Same Column in OBIEE

We have Customer Name Column and create date column which stores the date of customer activities. The requirement is to calculate the time between 2 adjacent activities created by the customer.

The operations need to be performed for achieving the above mentioned business problem is summarized below.

1. Group the created date by Customer
2. Oder the data in Ascending order
3. Calculate the difference between 2 adjacent dates to find the time between 2 activities created by a customer.

Solution

The above mentioned requirement can be achieved by using calling Analytic function (LEAD, LAG) using EVALUATE. The challenge here is to access multiple rows in a table without a self-join to the table. LEAD and LAG functions helps us aces multiple rows in a table.

The Syntax for LEAD and LAG are same and is mentioned below

    LAG (value_expression [, offset] [, default]) OVER ([query_partition_clause] order_by_clause)

    LEAD (value_expression [, offset] [, default]) OVER ([query_partition_clause] order_by_clause)

    value_expression - Can be a column or a built-in function, except for other analytic functions.

    offset - The number of rows preceding/following the current row, from which the data is to be retrieved. The default value is 1.

Now let’s see, how we can call these functions in OBIEE to achieve our requirement.

Customer Name - "Customer"."Customer Full Name"

Create Date - "Service Request"."Create Date"

To get the preceding date, use LEAD function and column formula is mentioned below

EVALUATE('LEAD(%1,1) OVER (PARTITION BY %2 ORDER BY %1)',"Service Request"."Create Date","Customer"."Customer Full Name")

Similarly use the LAG function to get the following date

EVALUATE('LAG(%1,1) OVER (PARTITION BY %2 ORDER BY %1)',"Service Request"."Create Date","Customer"."Customer Full Name")

Note

1. The columns Customer Name and Create Date should be present in the Analysis for the Evaluate to work. If any of the columns is removed, it will throw error. This is a bug that Oracle is currently working on.
2.  EVALUATE functionality should be enabled in OBIEE before using it.