3 Replies Latest reply on Apr 29, 2013 8:00 AM by user134954

    combined date sorting with anychart (mm.yyyy)

      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:


      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)
          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:
          so you will get 201201, 201202, 201203, ... so it can be sorted.

          • 2. Re: combined date sorting with anychart (mm.yyyy)
            Hilary Farrell-Oracle
            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 ?

            1 person found this helpful
            • 3. Re: combined date sorting with anychart (mm.yyyy)
              Thx Andy,

              this one works.
              Edited by: Thorsten on 29.04.2013 00:59