Analytics Software

Future readers, we can solve this by using a scalar subquery, iff there are guaranteed records in data_sales for entire year:

Each series Type: Region Source

select s.mth, s.val
  ,(select b.val
    from  data_budget b
    where s.mth = b.mth) budget
from data_sales s
order by mth

And respective series label chooses val/budget

Or here's an example that ensures always rolling 12 months for the series

with mths as
  (select add_months(trunc(sysdate,'mm'),-rownum) mth
  from dual connect by level<=12)
select mths.mth, val
from data_budget b
right outer join mths
on mths.mth = b.mth
order by mth

So workarounds exists, but documentation doesn't seem to match behaviour.

There's a feature in #orclapex 18.1 in JET charts: "Fill Gaps in Chart Data - Yes/No (defaults to Yes)"

Now that I'm running examples with actual data on 5.1.4, the problem seems worse

I played with the Time Axis Type plotting data points for half of March (current month):

Enabled - produces "invalid data", for both charts. The second is not densified within the SQL.

Automatic - still does not produce as expected

Disabled - using formatted date string, same output as Automatic.

In 5.1.3, this would be the only workaround to producing the expected chart. The only difference is now we don't have to densify the SQL ourselves.

Edit: 5.1.4 has impacted the ordering of existing JET charts that use the to_char(date_col) as dt_fmt workaround, so now they're alphabetical, regardless of query instructions.

So I'm now baulking at 5.1.4.

So we won't be going to 5.1.4.

What an interesting exercise this was, pulling together a demo across 5.1.3, 5.14, and 18.1.

And I think I learnt something right at the end about what it means to densify data for multi-series charts.

I've defined charts that use densified (date only) vs a simpler query, without the outer join; and a set for each relevant time axis option - disabled (with string label), enabled, automatic.

There are some bar graphs at the bottom to explore ordering.

All densified statements only generate a date range

with dts as (select trunc(date '2018-03-20')-rownum+1 dt from dual connect by level <= 31)

select null, d.dt dy

,count(s.rowid) c

,to_char(d.dt,'DD Mon') dt_fmt

,name

from jet_demo s

right outer join dts d

on d.dt = trunc(s.dt)

where d.dt >= date '2018-03-01'

group by d.dt

,name

order by d.dt

The only JET line chart that works as expected in 5.1.3 is the "fully dense" region, where I've generated data for not only the dates, but cross joining with all expected series

with dts as (select dt, name from (

select trunc(date '2018-03-31')-rownum+1 dt

from dual

connect by level \<= 31)

cross join (select distinct name from jet\_demo))

select null, d.dt dy

,count(s.rowid) c

,to_char(d.dt,'DD Mon') dt_fmt

,d.name

from jet_demo s

right outer join dts d

on d.dt = trunc(s.dt)

and d.name = s.name

where d.dt >= date '2018-03-01'

group by d.dt

,d.name

order by d.dt

Here is a sample in 5.1.4, as apex.oracle.com currently stands.

https://apex.oracle.com/pls/apex/f?p=32532:14:

It's great that the densified - disabled chart works in 5.1.4

as 5.1.3 seems to justify everything to the left. Same with the densified - enabled.

Note, the charts sometimes don't honour the date format selected in the x-axis.

Also note the ordering in this region, which is not a problem in 5.1.3, or 18.1

So here is the same page in 18.1 EA

https://apexea.oracle.com/pls/apex/f?p=32532:14

Quite a few of the charts still have problems, but the semi-densified SQL is rendered better.

However, here is the chart after it was freshly imported from 5.1.4, back to being an issue

I had to set this to No, save, then back to Yes again for it to behave.

This freshly imported app still exhibits this

https://apexea.oracle.com/pls/apex/f?p=1542:14

So perhaps the underlying meta-data may need to be treated better during import/instance upgrade/app upgrade?

Scott

I meant to explicitly mention that when I remove the date filter, so the dates bleed from late Feb, across to late March, then rendering is bad again

--where d.dt >= date '2018-03-01'

This sample removes the filter from the densified SQL

https://apexea.oracle.com/pls/apex/f?p=1536:14

Knowing that most of these examples so far can be solved by fully densifying the data helps, but some of our reports to tend to roll across date boundaries like this, or have generally scattered information across the y-axis, so we seem to see a lot of the weirdness - which is not fixed by full densifying the data in 18.1

Unless of course I have a query wrong, or attributes to adjust.

I'm really sorry but I found this topic a little bit hard to follow. Too many screenshots of charts based on unknown data and this mean I can't play with them. What is the problem (if there is no problem and its just series of experiments please ignore this message) in couple of words? Did you try Time Axis Type = Mixed Frequency? I've just read in item-level help that its for scatter and bubble charts only (maybe documentation bug sine JET JSDoc does not have such restrictions) but it's do work for line as well (tested with 5.1.4 and 18.1ea2)

Thanks Oleh, time for a summary of what I've found so far.

a) 5.1.3 multi-series densification needs to include series, not just date (x-axis)

b) still need to densify by date in 18.1

c) multi-series x-axis ordering had hiccup in 5.1.4

d) charts across rolling months may still be a problem

The sample pages I produced have the expected outcome at the top (as an anychart), and is probably the best way to view the differences/problems.

I have the same set of charts, varying by time axis type.

At the bottom I have a report of the data. Joel kindly asked for an example (on Twitter), so that's what I produced.

I've just made the data downloadable from the apex.oracle.com example.

I've also had mixed results with Mixed Frequency, and I wanted to try stay as documented.