Oracle Apex, charts question

I want to have a chart representing this weeks data vs historical data.

right now i have 2 queries / series for the chart..

the problem is, it's giving me the charts side by side, i would like to have the lines overlay each other.

is this possible?

this is what i have now
now
i would like something similar to this layout
would like
FutureDBA-Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

sdstuberCommented:
you could do that only if you create the same horizontal scale.

as long as your data points are tied to the actual dates, then "history" will always be before "current"


you could plot them to weeks or days of the year  rather than actual date

to_number(to_char(your_date,'DDD'))  would convert your dates to days of the year
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
FutureDBA-Author Commented:
So would I need 2 series? or would a single query achieve this for me?

this is the query i am using now for the single chart

with x as 

(select
trim(' ' from brand) brand, 
sldtrnprc, 
slddeldte,
sum(sldtrnqty*sldtrnprc) total_sales
from wf_rmsldp, wf_item where trim(' ' from slditmnum) = item 
and slddeldte between :P5_SDATE and :P5_EDATE and DESCR = :P5_ITEM

group by 
brand,
sldtrnprc,
slddeldte
order by slddeldte)

select null link, label, sum(value1) value1 from(
select 
null link, 
to_char(to_date(slddeldte,'yyyymmdd'), 'MON/YY') label,  total_sales value1
from x
where brand = :P5_BRAND)
group by label
order by to_date(label,'MON/YY') asc

Open in new window


Ideally, i would like between :p5_sdate and p5_edate represent the first line, the 2nd like would be represented for that same time frame, 1 year ago
0
FutureDBA-Author Commented:
I am plotting by months

to_char(to_date(slddeldte,'yyyymmdd'), 'MON/YY')
0
FutureDBA-Author Commented:
got the chart working, but query is messy.. not sure if you can simplify this..

with x as 

(select
trim(' ' from brand) brand, 
sldtrnprc, 
slddeldte,
to_char(to_date(slddeldte,'yyyymmdd'), 'MON/YY') xdate,
sum(sldtrnqty*sldtrnprc) total_sales
from wf_rmsldp, wf_item where trim(' ' from slditmnum) = item 
and slddeldte between 20120101 and 20140101 and DESCR = :P5_ITEM

group by 
brand,
sldtrnprc,
slddeldte
order by slddeldte)

select 
  null link, 
  substr(label,1,3) month, 
  sum(decode(substr(label,5,2),'12',value1)) date2012,
  sum(decode(substr(label,5,2),'13',value1)) date2013
from(
  select 
  null link, 
  to_char(to_date(slddeldte,'yyyymmdd'), 'MON/YY') label,  total_sales value1
  from x
  where brand = :P5_BRAND)
  group by substr(label,1,3), null
order by to_date(substr(label,1,3),'MON') asc

Open in new window

0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.