CFChart Problem

This may be a silly question, but I would like an answer!
If have a chart which shows the number of hours worked per week (see image attached).  The green line is last year's data and you can ignore the red line.  Our year starts in week 14 and so there is only one line returned by the query for this year so far showing 6315 hours (the orange line).  My code is below.  

My question is, why do I have an orange line that stays at the same level for the whole year as the current week.  (Should it not have the rest of the year showing at the bottom of the graph (point 0))?
<cfchart format = "flash" chartHeight = "500" chartWidth = "1000" showXGridlines = "yes" showYGridlines = "yes" showLegend="no" yAxisTitle = "Hours" sortXAxis = "no" showmarkers="no">

<cfchartseries query="QoQ_Qty" type="line" itemColumn="WkNo" valueColumn="TotQty" serieslabel=""> 

<cfchartseries query="QoQ_Qty#MYear#" type="line" itemColumn="WkNo" valueColumn="TotQty" serieslabel=""> 

<cfchartseries type="line" seriescolor="red">
 <cfchartdata value=8000>

Open in new window

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

cf interpolates any missing data points in the chart by default - that's why it 'inserts' same value 6315 for each data point that does not have actual data.
unfortunately, and silly enough, there is no setting to change this in cfchart tag - there is no attribute that controls this.

so you have 2 options depending on how you want to present the data:
1) add 0 as value for each missing data point. do this if you would rather have the line dip to 0 when there is no data. you will need to edit your query that gets the data to set the value to 0 when it is null. the syntax is db-specific, so let me know what db you are using and post your query as it is now if you need help with this.

2) turn of this default data interpolation by providing a custom xml stulesheet for your chart. do this if you rather want to have no line drawn at all for missing data points (i.e. the line will not dip to 0 - there will just be no line for a missing data point). attached is an example of how to set a custom style for your chart that switches the data interpolation off. note that i have changed showmarkers="no" to "yes" so that the single point of your orange line is visible on the chart (otherwise no line will be visible at all, since the whole line is just one data point).

<!--- create a custom xml chart style as myChartStyle var using cfsavecontent tag --->
<cfsavecontent variable="myChartStyle">
<?xml version="1.0" encoding="UTF-8"?>
<frameChart autoAdjust="false" is3D="false">
  <frame xdepth="1" ydepth="1" outline="black" />
  <elements place="Default" shape="Line" drawOutline="false" drawShadow="false" />
  <paint paint="Plain"/>
  <insets left="5" top="5" right="5" bottom="5"/>

<!--- use our custom style in the chart by setting <cfchart>'s STYLE attribute --->
<cfchart format="flash" chartHeight="500" chartWidth="1000" showXGridlines="yes" showYGridlines="yes" showLegend="no" yAxisTitle="Hours" sortXAxis="no" showmarkers="yes" STYLE="#myChartStyle#">

<cfchartseries query="QoQ_Qty" type="line" itemColumn="WkNo" valueColumn="TotQty" serieslabel=""></cfchartseries>

<cfchartseries query="QoQ_Qty#MYear#" type="line" itemColumn="WkNo" valueColumn="TotQty" serieslabel=""></cfchartseries>

<cfchartseries type="line" seriescolor="red">
 <cfchartdata value=8000>

Open in new window


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
jdthedjAuthor Commented:
Thanks Azadi - that's amazing!  I have gone with the XML solution.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ColdFusion Language

From novice to tech pro — start learning today.