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.
1 person found this helpful
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.
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
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
So perhaps the underlying meta-data may need to be treated better during import/instance upgrade/app upgrade?
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
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.