Wednesday 15 June 2016

Charting Predictions, al a AskTom

The 'grand algorithm' favoured this particular tweet from Connor McDonald in my 'highlights'.
I found this intriguing considering a side project I've been tinkering on. The solution ended up looking much simpler than a model clause, though I'm going to need to let it digest for a while before I fully understand how it works. Maybe read the relevant documentation.

Trouble is, the final result isn't the sort of thing you want to print out on your dot matrix printer to show the big cheese, right? You want to graph that sucker.

So I create a page in Oracle APEX 5.1 (apexea.oracle.com) and created a line chart.

I split the results into two series using Connor's query within an inline view, in part because that was the fastest way I could think to get the graph plotted
select dte
 ,sz as actual
from (
with reg as (
    select regr_slope(sz, to_number(to_char(dte,'j'))) slope,
           regr_intercept(sz, to_number(to_char(dte,'j'))) intcpt,
           max(dte) nxt
  from t
  )
  select t.*, 'actual' tag
  from   t
  union all
  select nxt+r dte,
         trunc(intcpt+slope*to_number(to_char(nxt+r,'j')))
         ,'predicted'
  from reg,
       ( select rownum r
         from   dual
         connect by level <= 30 )
  order by 1
)
where tag ='actual'
The result was compelling.

A picture paints a thousand words

I also like the fact the declarative series name honoured, rather than a double quoted "Column Alias".

It will be interesting to see how this plots more varied data, considering the data variance issues Connor described.

I added a to_char(dte, 'DD Mon YYYY') to get a prettier tooltip, but interestingly, it changed the angle of the line.

TO_CHAR() added

Then I spotted this field here, I could use the query as supplied by Connor without having to think about pivoting the result to get it into the right format the engine requires.

A new declarative option

However the result didn't come out quite right. I think that's more an issue with the communication between APEX and OracleJET that anything else, ie: a bug.

Not quite right

It appears the declarative charting options have been engineered in 5.1 to reduce the amount of hoop jumping with the result set. Charting in general appears more declarative, easier to navigate and work out what's going on.

Nice work APEX team.

2 comments:

Hilary Farrell said...

Hi Scott,

Great to see you're putting our new charts in APEX 5.1 to the test. Thanks for taking the time to try them out, and with a really nice example too! I've taken a look at your last 'Not quite right' example, and can explain why you're seeing those results. Your chart is representing Date/Time information on the X-Axis, so you need to set the Time Axis Type chart-level attribute so that Oracle JET knows how to interpret your data. Given that the 'Combined' series is representing data with mixed frequency i.e. each series has differing dates, that attribute should be set to Mixed Frequency. Then you'll see the chart rendering as expected. Check out page 37 of your application, where I've added the working examples.
Thanks again for taking time out to try our EA instance, it's hugely appreciated.

Regards,
Hilary
Oracle Application Express Team

Scott Wesley said...

Thank you for your help, Hilary. That's an interesting setting that will help overcome some existing issues. Looks like the help may need updating, since Mixed Frequency is reported as only being for Scatter/Bubble charts?