Thursday, December 26, 2013

OBIEE - Local minima and maxima in graph

Local minima and maxima (extrema) in graphs emphasis the points where the graphs change the trend. For example (green dots are the local maxima and red dots are the local minima):


How to achieve it in OBIEE?

Assuming local maxima is the point that is higher then the 2 neighbors it has (value on the left and on the right), and local minima is the point that is lower then the 2 neighbors. And assuming most cases are to do with time progress; I can easily find them using PERIODROLLING function. For example, I created an Analysis based on Sample  sales with 2 columns: Month and Revenue.
To the 2 columns I'll add local minima and local maxima calculation. In this case local min is:
case when 
        "Base Facts"."1- Revenue"<PERIODROLLING("Base Facts"."1- Revenue", -1 ,-1) and 
        "Base Facts"."1- Revenue"<PERIODROLLING("Base Facts"."1- Revenue", 1 ,1) 
     then "Base Facts"."1- Revenue" 
     else NULL 
end

To make it more readable what that means is:
Case when 
               "Revenue"<"Revenue one period ago" and 
               "Revenue<"Revenue next period" 
         then "Revenue" 
         else NULL 
end

The same goes with local maxima, anly use > instead of <.

So the result is:

I used conditional format on the min and max points. (Please note it's a simplistic calculation that omits the first and last points in a graph.)

Now, in "graph properties" -> "Style and Conditional Formatting"  I can create a graph with 3 lines, where the first line is line and the second and third are symbols only:




and the result is:

Something similar can be created with Line Bar graph:
(I created a unified scale and changed the colors).

The funny part is, after I did this I had a complaint there are too many local extrema points. And was requested to mark only the point that are lower / higher then 2 neighbors in each direction:

The result is:

And the function is:
case when 
    "Base Facts"."1- Revenue"<PERIODROLLING("Base Facts"."1- Revenue", -2 ,-2) and
    "Base Facts"."1- Revenue"<PERIODROLLING("Base Facts"."1- Revenue", -1 ,-1) and 
    "Base Facts"."1- Revenue"<PERIODROLLING("Base Facts"."1- Revenue", 2 ,2) and 
    "Base Facts"."1- Revenue"<PERIODROLLING("Base Facts"."1- Revenue", 1 ,1) 
  then "Base Facts"."1- Revenue" 
  else NULL 
end
And the full picture:



By the way. A very important information in line graph analysis is the Linear regression. Here are 2 post on the subject:
By Kurt Wolff, that should work on any data source.
From this blog, specific to Oracle DB functions. 
.

2 comments: