Frequently User ask to compare last row results. If the dimension column is either Date,Week,Month,Qtr or Year then we can go with Time series Functions to compare the results.
But the dimension column is not an period and the requirement is to compare results between Offices or LOBs then Time series functions are helpless.
In that case we can use LAG function.
---------------------
Office wise Order details are as follows,
Office Key || Office || No.Orders
===========================
1 || ABC || 100
2 || XYZ || 200
3 || MNO || 150
User want to compare Orders by Office.
Office Key || Office || No.Orders || Last Row Orders || Diff
=============================================
1 || ABC || 100 || 0 || 100
2 || XYZ || 200 || 100 || 100
3 || MNO || 150 || 200 || -50
Solution
-------------------
LAG function is not OBIEE function, so we can perform this using Evaluate function.
EVALUATE('LAG(%1,1)over(order by CAST(%2 AS INT))',"Calculated Facts"."# of Orders","More Office Objects"."Office Key")
Here we are comparing Number of Orders by last row results by office Key.
LAG(%1,1)over(order by CAST(%2 AS INT))
Where %1 is , Measure i.e No Of Orders
1 is, last row (if we give 2 then comparing current row and 2nd row result from current row)
%2 is, Office Key
Where "Diff" is (# of Orders) - (Previous # of Orders).
If Time series functions are not created then we can use LAG function.
