Logarithmic Apex Charts

May 25, 2012 at 3:31 pm | Posted in Oracle Application Express (Apex), Oracle Developement, PL/SQL | 1 Comment
Tags: , , , , , ,

If you have a chart with a large difference between the highest and lowest values, you may find that the low values disappear and become unclickable.

For the charts below I used the following data:

NAME VALUE
China 1,347,350,000
India 1,210,193,422
USA 313,606,000
Brazil 192,376,496
Germany 81,859,000
Finalnd 5,409,870
Luxemburg 511,800
Monaco 35,881
Vatican City 800
Coco 605
Country Populations Linear Scale

Country Populations Linear Scale

In order to work around this situation, you can display your chart with a logarithmic scale, rather than a linear one. The logarithmic scale will squeeze the large values and stretch the low values. This will allow those low values to remain visible and clickable. In the graph below, I used the following XML in the custom Chart XML:

<y_axis >
  <scale type="Logarithmic" />
Country Populations Base 10

Country Populations Base 10

Sometimes however, even in a logarithmic scale, low values can still disappear from view, so you need to adjust the log base to match the value range. To calculate the correct log base you need to find the geographic mean of all your charted values.

The next chart was created from the following values:

NAME VALUE
Mercedes 50000
Mazda 25000
Kia 13000
Canon 1D 5000
Bass Guitar 1000
Watch 300
Ticket 100
Dinner 50
Bar 50
Tip 10
Stuff Costs Linear

Stuff Costs Linear

Now if we just use the default log base of 10, we’ll get the following:

<y_axis >
  <scale type="Logarithmic" />
Stuff Costs Log Base 10

Stuff Costs Log Base 10

The last column(Tip – value 10) remains hidden and is un-clickable.

If we now calculate the geometric mean according to the formula below, well get 755.

Geometric Mean

Geometric Mean

We now plug that number into our custom XML, and the last column appears:

<y_axis >
  <scale type="Logarithmic" log_base="755" />
Stuff Costs Log Base 755

Stuff Costs Log Base 755

To calculate the geometric mean in PL/SQL, you can use the function below. You’ll have to pass a ref cursor with the list of values from your data set, and it will return a single number, which is the geometric mean.

-------------------------------------------------------------------------------
-- Christoph Ruepprich cruepprich@gmail.com
  --
-- The geometric mean is used to set the mid-point of logarithmic charts.
-- The argument p_fudge (fudge factor) is there to raise the mean by an
-- arbitrary number. This is done to account for rounding errors caused by
-- the precision limit of the NUMBER data type.
-- Typically you would want to use a number < 1. For example .1 would raise
-- the geometric mean by 10%, which should be enough to make up for rounding
-- errors.
-- Usage:
-- Generate a ref cursor of the set of values in a chart, and pass that
-- cursor to the function.
-------------------------------------------------------------------------------

CREATE OR REPLACE FUNCTION get_geometric_mean(p_cur SYS_REFCURSOR, p_fudge NUMBER DEFAULT 0) RETURN NUMBER IS
  l_mean NUMBER := 1;
  l_cnt  PLS_INTEGER;
  TYPE t_array IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
  l_arr t_array;
BEGIN

  FETCH p_cur BULK COLLECT
  INTO l_arr;
  l_cnt := l_arr.count;

  FOR idx IN l_arr.first .. l_arr.last LOOP
    IF l_arr(idx) <> 0 THEN
      l_mean := power(l_arr(idx),1/l_cnt) * l_mean;
    END IF;
  END LOOP;

  l_mean := trunc(l_mean * (1 + p_fudge));

  RETURN l_mean;
END get_geometric_mean;

Usage:

DECLARE
  l_cur SYS_REFCURSOR;
  l_sql varchar2(4000);
  l_gm  NUMBER;
BEGIN
  l_sql := 'SELECT value FROM table_with_chart_data';

  OPEN l_cur FOR l_sql;
  l_gm := get_geometric_mean(p_cur   => l_cur);

  dbms_output.put_line('Geometric mean: ' || l_gm);
END;
Advertisements

1 Comment »

RSS feed for comments on this post. TrackBack URI

  1. […] Another highlight was the Apex open mic night, where people were given 10 minutes to show off cool things you can do with Apex. There I had a chance to show off my Logarithmic chart trick. […]


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.
Entries and comments feeds.

%d bloggers like this: