This discussion is archived
3 Replies Latest reply: Apr 29, 2013 1:00 AM by user134954 RSS

combined date sorting with anychart (mm.yyyy)

user134954 Newbie
Currently Being Moderated
Hi there
[apex 4.2, Oracle 11g]

I have a table in which I extract the year and the month and both in combination (from a date type). This is needed to summarize for example the year in a chart.

I need the combination of month and year because people could do a query which switches through years. For expample: From 02.2012 to 03.2013.

My problem is I dont know how to tell anychart to sort the dates.

I have already tried:
to_number(to_char(testdate,'YYYY')) as Year,
to_number(to_char(testdate,'YYYY'))||to_number(to_char(testdate,'MM')) as Monthyear
Apex now sorts:

20121,201212,20122


I would like that apex realizes to order:

1.2012,2.2012,3.2012 .........01.2013,02.2013.03.2013


The report itself has now four date relating collums:

1) Date (Type Date)
2) Year (Type Number)
3) Month (Type Number)
4) Monthyear (Type varchar)



Hope you can help.
  • 1. Re: combined date sorting with anychart (mm.yyyy)
    user12087803 Newbie
    Currently Being Moderated
    Hi,
    I am not sure if the anychart is doing the sorting. But i am also very new to apex and charts.
    Try to sort the query-result in the way you want with order by testdate.

    If it does not work you can change your query:
    to_number(to_char(testdate,'YYYY')||to_char(testdate,'MM'))
    so you will get 201201, 201202, 201203, ... so it can be sorted.

    brgds.
    Andy
  • 2. Re: combined date sorting with anychart (mm.yyyy)
    Hilary Expert
    Currently Being Moderated
    Hi Thorsten,

    You might find it useful to refer to the 'Sample Database Application', which gets installed in your workspace as part of a workspace provisioning. Edit the application, and view the chart series query for the 'Sales by Category/Month' chart on pg 5, where you'll see how the representation of the DATE information has been handled, which uses the following query:
    select null, 
           to_char(o.order_timestamp, 'MON RRRR') label, 
           sum (decode(p.category,'Accessories',oi.quantity * oi.unit_price,0)) "Accessories"
    from demo_product_info p, demo_order_items oi, demo_orders o
    where oi.product_id = p.product_id
    and o.order_id = oi.order_id
    group by to_char(o.order_timestamp, 'MON RRRR'), to_char(o.order_timestamp, 'RRRR MM')
    order by to_char(o.order_timestamp, 'RRRR MM')
    You may want to try something similar in your query. Try it out in SQL Developer or SQL Workshop first, to verify the order of results.

    BTW is it safe to assume that your other open thread has now been answered: Re: Select x-axis into chart ?

    Regards,
    Hilary
  • 3. Re: combined date sorting with anychart (mm.yyyy)
    user134954 Newbie
    Currently Being Moderated
    Thx Andy,

    this one works.
    (to_char(testdate,'YYYY')||'-'||to_char(testdate,'MM')) 
    Edited by: Thorsten on 29.04.2013 00:59

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points