1 Reply Latest reply on Jun 4, 2008 10:51 AM by Dan Bracuk

    Can you graph both daily and weekly data on the same cfchart?

      I've been searching the internet for any help with this issue, but have come up sort so far. I'm hoping that someone in this group may be able to help.

      I have four series of data that I want to create a line graph with using the cfchart functionality. All four series are date/value pairs. Where it gets problematic is that two of the series have daily data points while the other two only have weekly data points. ColdFusion doesn't seem able to handle extrapolating the values for the missing data points in the weekly series.

      I'm currently using ColdFusion MX 7 with the data stored in a MySQL 5.0 database.

      The end result that I'm trying to achieve is for the graph to show both the daily and weekly data. All the daily data should be displayed and the weekly data points should be displayed as well (even though there are fewer data points, they should be connected with lines in the graph). Is this something that can be done with ColdFusion?

      The closest I've come to finding a solution someone indicated that the series must have the same scale. How would I accomplish this without having to manually extrapolate the daily data values for the weekly series?
      Link: macromedia.coldfusion.advanced_techniques on Google Groups

      Also, on Experts Exchange, I found a similar post about how to handle missing data points, however it relied on modifying the SQL query to include the text "No Entry" for missing data points. Unfortunately, whenever I try that solution, I get SQL errors. I'm guessing that the "No Entry" trick must be for a different database platform.
      Link: Experts Exchange CF Topic

      Right now, the only option I see open to me is to manually extrapolate the daily values for the missing data points in the weekly series, not something I wanted to try to do.

      Any advice would be greatly appreciated.
        • 1. Re: Can you graph both daily and weekly data on the same cfchart?
          Dan Bracuk Level 5
          This answer addresses the missing data points. Most dbs have a function with a name like ifnull, nvl, or coalesce that says to the db, get me this if it's there, otherwise get me that. I don't use mysql so I don't know if they have one or what it's called.

          However, the datatypes have to match. In other words, you can't have
          select ifnull(field1, 'no entry')
          if field1 is numeric.

          One of my pages graphs data for date range, and then the same date range for the previous two years. I get my gap with Feb 29, and just leave it as a gap.