Forum Stats

  • 3,875,295 Users
  • 2,266,907 Discussions
  • 7,912,141 Comments

Discussions

Graph questions

Javier P
Javier P Member Posts: 888 Silver Badge

Hi all,

In Apex 21,2

I would like to show a graph with the last 12 months of production.

This is my query:

select null,

  sum(f.comision) value,

   TO_CHAR(f.efectiva,'Mon') label

from risk_facturas f

 where f.efectiva >= add_months(trunc(sysdate), -13) and f.efectiva < trunc(sysdate) + 1

   and f.organizacion_id = :APP_GET_ORGANIZACION_ID

   group by TO_CHAR(f.efectiva,'Mon')


This is the graph am getting:

Two questions:

  1. The graphs is not showing all 12 months. Is truncating the graph and shoing only six months even when I have nothing in Maximum Width of the graph.
  2. How can I get the months in order?

Thanks for the help!

Javier

Answers

  • fac586
    fac586 Senior Technical Architect Member Posts: 21,470 Red Diamond

    The graphs is not showing all 12 months. Is truncating the graph and shoing only six months even when I have nothing in Maximum Width of the graph.

    How can I get the months in order?

    Both questions are probably related to incorrect handling of the date dimension.

    Do not convert the date value to a character string—keep its data type as DATE, and configure the chart to render the data on a temporal axis:

    select
        null
      , sum(f.comision)         value
      , trunc(f.efectiva, 'MM') label
    from
        risk_facturas f
    where
        f.efectiva >= add_months(trunc(sysdate), -13) and f.efectiva < trunc(sysdate) + 1
    and f.organizacion_id = :APP_GET_ORGANIZACION_ID
    group by
        trunc(f.efectiva, 'MM')
    

    In the chart region attributes:

    • Enable the Fill Gaps in Chart Data setting
    • Set Time Axis Type to Enabled

    In the X Axis attributes:

    • Set Format to Date - Short
    • Set Pattern to MMM

    Note that—very annoyingly—JET chart date formatting uses Unicode CLDR patterns, not Oracle format masks. The documentation for this was dire, and is now even worse after a recent "migration".

    Since we don't have access to your tables, there may be other data-related issues.

    Javier P
  • Javier P
    Javier P Member Posts: 888 Silver Badge

    Thanks fac586!

    It fixed the order but now is not showing months without any data.

    Also, is still truncating the graph.


    What other advise can you give me?

    Thanks a lot for your help!

    Javier

  • fac586
    fac586 Senior Technical Architect Member Posts: 21,470 Red Diamond

    What other advise can you give me?

    Demonstrate the problem in a shared workspace on apex.oracle.com.

  • Javier P
    Javier P Member Posts: 888 Silver Badge

    Ok. I will create it.

    Thanks

  • Javier P
    Javier P Member Posts: 888 Silver Badge

    Hi fac586,

    The problem is what you said, the data.

    The query only returns the months that have a value therefore the graph just shows those months.

    Is there a way that the query can return the 12 months no matter if they have any production?


    Thanks

  • fac586
    fac586 Senior Technical Architect Member Posts: 21,470 Red Diamond

    Is there a way that the query can return the 12 months no matter if they have any production?

    As indicated the chart settings described above should fill in gaps in the data automatically, as seen in this example:

    Try setting Time Axis Type to Mixed Frequency.