4 Replies Latest reply: Aug 27, 2013 2:35 AM by Jezzer RSS

    Line chart to ignore null values

    Jezzer

      Hi,

       

      I have a table, RELIABILITY, which holds data for part reliability, so it has the following columns:

       

      REL_DATE,

      PART_NO

      MTBF

       

      This shows the Mean Time Before Failure for a part. Data is put into the table when a part fails, so there is not necessarily data for every month for a part.

       

      I want to create a line chart which shows this, with lines connecting the points.

       

      The problem I have is that say I have the following data:

       

      REL_DATE - 01-JAN-10

      PART_NO - APART

      MTBF - 10

       

      REL_DATE - 01-MAR-10

      PART_NO - APART

      MTBF - 20

       

      If I chart this, I get two points mapped correctly, but because there is no data for 01-FEB-10 it does not join the points up. Bear in mind this is a very simplified example.

       

      Is there anyway to tell APEX to ignore the "null" data for 01-FEB-10, and join up the points for JAN and MAR, so that at FEB it the line would go through 15 for MTBF?

       

      Ideally I want to be able to do this without having to calculate a dummy value of 15 for FEB, as I would rather not put a point there as there is no actual data.

       

      Any ideas?

       

      Regards,

       

      Jez.

        • 1. Re: Line chart to ignore null values
          Howard (... in Training)

          Before I go research this, let's think about it a little.  If the value for Feb. is NULL, where is NULL on the chart?  It isn't anywhere, of course, so (I suppose) that's why it can't be plotted.  And you can't make the value 0 because then the line would go thru 0. You could compute a fake value which was the midpoint of the line between Jan and Mar -- I suppose that's what you want.  But the data doesn't suppose that faked value.  Just some thoughts.

           

          Howard

          • 2. Re: Line chart to ignore null values
            srbonham1

            why not filter out the null values in the query underlying the chart?

            • 3. Re: Line chart to ignore null values
              srbonham1

              or, you could use:

               

              select

              nvl( mtbf , (select max(mtbf) from reliability where mtbf is not null and rel_date < REL.rel_date ) ) mtbf

              from reliability REL

               

              this query, assuming you are ordering by date, should work to grab the last mtbf that has a value thus passing through to the next plot without messing up your data

              • 4. Re: Line chart to ignore null values
                Jezzer

                Thanks for the responses.

                 

                The RELIABILITY table does not have an entry for every month, just when a unit fails, so if I was looking at the reliability between say 01-JAN-09 and 31-DEC-09, I might have values for JAN and MAR but not for FEB. Whilst I could work out the midpoint for FEB, i.e. the middle between JAN and MAR, ideally I do not want to actually plot a point here as there is no data. I just want the line to go through that point. Hope that makes sense.

                 

                As for omitting the null values. The previous chart the client had did not show all months for the period selected, which meant the scale was not right. I want the scale on the x-axis to show all months for the selected period. This meant that I have had to say get data for all months, even if there is no data for that month. This is what has meant that I then have null values and so the line then does not show.

                 

                The query that you suggested would get the maximum mtbf where the date is less that my current plotting date, which is not necessarily right. And, as just mentioned, this means I would plot a point where I do not actually have any data.

                 

                So in essence I am saying I want to plot points for JAN and MAR, and not for FEB, and I want the JAN and MAR points to be joined by a line which goes through FEB, implying that FEB would have been the mid point between those two values. i.e. just join up all the points that I have thus ignoring the fact that there is "missing" data for a month.

                 

                Any more thoughts?

                 

                Thanks,

                 

                Jeremy.