3 Replies Latest reply on Jul 21, 2017 12:59 PM by R. Buitenhuis

    How to create a correct stacked bar chart with 'missing' values?

    R. Buitenhuis

      I use the following query for a APEX 5.1.1 stacked bar chart:

       

      select 2015 as label, 'Rent' as series, 548 as value from dual

      union

      select 2016, 'Rent', 553 from dual

      union

      select 2017, 'Rent', 561 from dual

      union

      select 2016, 'Car', 7385 from dual

      union

      select 2015, 'Furniture', 1673 from dual

      union

      select 2017, 'Furniture', 2385 from dual

       

      As you can see there are no values for 'Car' in 2015 and 2017 and no value for 'Furniture' in 2016.

       

      The chart I get looks like this:

      Demo wrong stacked bar.png

       

      This is not what I expected. So what am I doing wrong?

       

      When I add the missing values using this query:

      select 2015 as label, 'Rent' as series, 548 as value from dual

      union

      select 2016, 'Rent', 553 from dual

      union

      select 2017, 'Rent', 561 from dual

      union

      select 2015, 'Car', 0 from dual

      union

      select 2016, 'Car', 7385 from dual

      union

      select 2017, 'Car', 0 from dual

      union

      select 2015, 'Furniture', 1673 from dual

      union

      select 2016, 'Furniture', 0 from dual

      union

      select 2017, 'Furniture', 2385 from dual

       

      Then I get the correct chart:

      Demo right stacked bar.png

      Is there a way to accomplish this without adding the 'missing'values?

        • 1. Re: How to create a correct stacked bar chart with 'missing' values?
          fac586

          R. Buitenhuis wrote:

           

          I use the following query for a APEX 5.1.1 stacked bar chart:

           

          select 2015 as label, 'Rent' as series, 548 as value from dual

          union

          select 2016, 'Rent', 553 from dual

          union

          select 2017, 'Rent', 561 from dual

          union

          select 2016, 'Car', 7385 from dual

          union

          select 2015, 'Furniture', 1673 from dual

          union

          select 2017, 'Furniture', 2385 from dual

           

          As you can see there are no values for 'Car' in 2015 and 2017 and no value for 'Furniture' in 2016.

           

          The chart I get looks like this:

          Demo wrong stacked bar.png

           

          This is not what I expected. So what am I doing wrong?

           

          When I add the missing values using this query:

          select 2015 as label, 'Rent' as series, 548 as value from dual

          union

          select 2016, 'Rent', 553 from dual

          union

          select 2017, 'Rent', 561 from dual

          union

          select 2015, 'Car', 0 from dual

          union

          select 2016, 'Car', 7385 from dual

          union

          select 2017, 'Car', 0 from dual

          union

          select 2015, 'Furniture', 1673 from dual

          union

          select 2016, 'Furniture', 0 from dual

          union

          select 2017, 'Furniture', 2385 from dual

           

          Then I get the correct chart:

          Demo right stacked bar.png

          Is there a way to accomplish this without adding the 'missing'values?

          No, there must be an entry for each series in every group.

           

          • 2. Re: How to create a correct stacked bar chart with 'missing' values?
            Hilary Farrell-Oracle

            Hi Robin,

             

            I hope you're keeping well.  I'm not sure if you were able to attend my session at the conference in March, but this very topic came up.  The current behaviour, while intentional, is something that has caught out a number of people.  Just to explain - the chart data for a JET chart is handled via a JSON object.  JET expects that each series of a multi-series chart contains a data point for each x-axls label - otherwise, it simply renders the data points in the order in which they occur in the JSON object, even if some points are missing - which can lead to incorrect results.  For 5.1, we chose to leave the handling of 'densifying' the data to the customer, but we've taken onboard the feedback from customers on this forum and at conferences.  So for our upcoming 5.1.3 release, we do hope to have a built-in solution to inject the null values into the dataset.

             

            In the meantime, you'll need to handle the injection of null values/data points via the SQL query - as you have done above.

             

            Regards,

            Hilary

            • 3. Re: How to create a correct stacked bar chart with 'missing' values?
              R. Buitenhuis

              Hi Hilary,

               

              Thanks for your answer. I am looking forward to the upcoming release.

               

              Kind regards,

              Robin