I am trying to create a graph based on a partial set of data, however I don't seem to be able to get ColdFusion's interpolation feature to work. Basically, I have a daily timeline, however not every day has a value to be graphed. My specific question is: how do I tell the cfchart to ignore the missing data and yet still have a proper looking graph?
Hopefully, this code will help you understand my question more... <cfquery name="pointActuals" datasource="tsr"> SELECT DATE, SUM(POINTS_ATTEMPTED + POINTS_FAILED + POINTS_COMPLETED + POINTS_PERMFAILED) AS TOTAL_POINTS_ATTEMPTED, SUM(POINTS_COMPLETED + POINTS_PERMFAILED) AS TOTAL_POINTS_COMPLETED FROM DATA WHERE IBM_EMAIL = 'project' AND PROJECT_ID = '6' GROUP BY DATE ORDER BY DATE ASC </cfquery> <cfquery name="pointProjections" datasource="tsr"> SELECT DATE, POINTS_ATTEMPTED, POINTS_COMPLETED FROM PROJECTIONS WHERE PROJECT_ID = '6' ORDER BY DATE ASC </cfquery> <!--- Create an array to hold all the dates ---> <!--- 1) date 2) actual total points attempted 3) actual total points completed 4) projected total points attempted 5) projected total points completed ---> <cfset valuesArray = ArrayNew(2)> <cfset counter = 1> <cfloop index="date" from = "4/21/2008" to = "8/19/2008" step="#CreateTimeSpan( 1, 0, 0, 0 )#"> <!--- Set the date for the array ---> <cfset valuesArray[counter][1] = "#CreateODBCDateTime(date)#"> <cfset matchedActualDate = FALSE> <cfset matchedProjectionDate = FALSE> <!--- Set the actual points ---> <cfloop from="1" to="#pointActuals.RecordCount#" index="actualsDate"> <cfif #CreateODBCDateTime(date)# EQ #pointActuals.DATE[actualsDate]#> <cfset valuesArray[counter][2] = pointActuals.TOTAL_POINTS_ATTEMPTED[actualsDate]> <cfset valuesArray[counter][3] = pointActuals.TOTAL_POINTS_COMPLETED[actualsDate]> <cfset matchedActualDate = TRUE> </cfif> </cfloop> <!--- If we didn't find a match for the date, use a null value ---> <cfif NOT matchedActualDate> <cfset valuesArray[counter][2] = "0"> <cfset valuesArray[counter][3] = "0"> </cfif> <!--- Set the projected points ---> <cfloop from="1" to="#pointProjections.RecordCount#" index="projDate"> <cfif #CreateODBCDateTime(date)# EQ #pointProjections.DATE[projDate]#> <cfset valuesArray[counter][4] = pointProjections.POINTS_ATTEMPTED[projDate]> <cfset valuesArray[counter][5] = pointProjections.POINTS_COMPLETED[projDate]> <cfset matchedProjectionDate = TRUE> </cfif> </cfloop> <!--- If we didn't find a match for the date, use a null value ---> <cfif NOT matchedProjectionDate> <cfset valuesArray[counter][4] = "0"> <cfset valuesArray[counter][5] = "0"> </cfif> <cfset counter = counter + 1> </cfloop> <cfoutput> <cfdump var=#valuesArray#> </cfoutput> <cfchart title="Point Projections vs Actuals" chartheight="500" chartwidth="700" showxgridlines="no" showygridlines="yes" showborder="yes" fontbold="no" fontitalic="no" xaxistitle="Date" yaxistitle="Points" show3d="no" rotated="no" sortxaxis="no" showlegend="yes" showmarkers="no"> <cfchartseries type="line" serieslabel="Actual Attempted" paintstyle="light"> <cfloop from="1" to="#arraylen(valuesArray)#" index="i"> <cfchartdata item="#DATEFORMAT(valuesArray[i][1], 'yyyy-mm-dd')#" value="#valuesArray[i][2]#"> </cfloop> </cfchartseries> <cfchartseries type="line" serieslabel="Actual Completed" paintstyle="light"> <cfloop from="1" to="#arraylen(valuesArray)#" index="i"> <cfchartdata item="#DATEFORMAT(valuesArray[i][1], 'yyyy-mm-dd')#" value="#valuesArray[i][3]#"> </cfloop> </cfchartseries> <cfchartseries type="line" serieslabel="Projected Completed" paintstyle="light"> <cfloop from="1" to="#arraylen(valuesArray)#" index="i"> <cfchartdata item="#DATEFORMAT(valuesArray[i][1], 'yyyy-mm-dd')#" value="#valuesArray[i][4]#"> </cfloop> </cfchartseries> <cfchartseries type="line" serieslabel="Projected Completed" paintstyle="light"> <cfloop from="1" to="#arraylen(valuesArray)#" index="i"> <cfchartdata item="#DATEFORMAT(valuesArray[i][1], 'yyyy-mm-dd')#" value="#valuesArray[i][5]#"> </cfloop> </cfchartseries> </cfchart> The values I'm getting from my database query for TOTAL_POINTS_ATTEMPTED, TOTAL_POINTS_COMPLETED, POINTS_ATTEMPTED, AND POINTS_COMPLETED are just integers saved in my MySQL database. While I realize that in my example above I am setting the value of missing data to "0" (which isn't what I want), if I try any other value (such as "" or NULL), I get various ColdFusion errors. The only potential solution I see right now is to manually extrapolate the expected values for the missing data and save it in my array, however I just seems like that's too much work and that ColdFusion probably has some way to handle this situation. I would *greatly* appreciate any help or pointers you could provide. Thank you for taking the time to read my question. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;203748912;27390454;j Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:308284 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
