4 Replies Latest reply on Nov 18, 2009 12:28 PM by kodemonki

    Can You Create Trend Line in CFCHART?

    Gary1 Level 1
      Have pretty much got CF7 Charts and WebCharts3D down, at least to the point where they can do what I need the most. However, the one thing I'd like to be able to do, but not sure if it's possible, is create a TREND LINE.

      This would be a LINE charts with 2 series. The first series would be a line with the actual counts covering a period of time.
      I'd like to add a 2nd line, using the same data from the first series, but have it displayed as a TREND line. In Excel, you can easily create a TREND line, it's even offered as an option.

      I've seen somehwere (CFCHART or WebCharts3D) something to so with "logarithmic" options, which is one of the options for Excel charts.

      Two more quick questions:
      Q1. Is there any way to control the margin between the left and right borders of the charts, and the bars? Have tried experimenting, but can't seem to get this to work. Reason is, when I try to show a chart in 3D, the first bar on the left is "butted up" against the left chart border. Would like to add a little space there.

      Q2. Rather than display rectangular bars, I'd like to display rounded cylinders. I've tried setting the chart type in CFCHART to "cylinder" but it still displays a bar. When I try to change the type to "cylinder" in WebCharts3D, the chart STILL displays rectangular bars. Yet I've seen many examples of rounded, cylinder bars.

      Don't mean to push my luck with too many questions. Thanks for any advice, help.
        • 1. Re: Can You Create Trend Line in CFCHART?
          MikerRoo Level 1
          1. Trend Lines:
            CFChart does not do trend lines of any type. However, it is not too difficult to compute the regression of your choice and to plot it as a second series.
          2. Logarithmic:
            Not sure what you are asking here. In theory, cfchart does do log-lin and log-log charts.
            However, it is so buggy, and the scaling problems are so great, that we have never been able to get it to work well.
            We use ChartDirector for anything that requires a log scale.
          3. Side margins:
            You cannot control these directly. Here are some workarounds:
            1. For "Category" axis:
              1. Make sure the "isBucketed" property is set to true (this is the default).
              2. If that is not enough, add extra bars to your data series, usually value zero. It's not ideal but it may be good enough.
            2. For "Scale" axis:
              1. Usually this works well enough. Otherwise, try toggling the "isAbsolute" property.
              2. The only other recourse is to manually scale the plot using the "scaleMin" and "scaleMax" properties. This can be a real pain for obvious reasons and also because cfchart scaling is fraught with bugs and "quirks".
          4. Cylinder Bars:
            These work. Perhaps there was a setting conflict within the XML or between the CFChartSeries setting and the XML?
            Also the cylinders will only look round when 3D is turned on. (Side view of a cylinder is just a shaded bar.)

          Anyway, I've said it before: cfchart is barely usable for a very limited range of charts.
          For real business or common engineering and scientific needs, you need to step up to something better.
          The package we use (has all the features and very reasonably priced) is ChartDirector ( http://www.advsofteng.com/cdcoldfusion.html). (And, no, I'm not affiliated.)
          • 2. Re: Can You Create Trend Line in CFCHART?
            davidmedifit Level 1
            If you have CF7 installed, research "WebCharts3D" - it is a free package included with CF7. I'm not sure it will meet all of your needs, but it certainly is a step up from regular charts.

            http://livedocs.macromedia.com/coldfusion/7/htmldocs/wwhelp/wwhimpl/common/html/wwhelp.htm ?context=ColdFusion_Documentation&file=00001431.htm


            • 3. Re: Can You Create Trend Line in CFCHART?
              Gary1 Level 1
              Thanks MikerRoo. It's just good to know I wasn't missing some "option" in WebCharts3D that helped control the margins. I'll try your suggestions. Sounds like you've had some experiene with WebCharts3D. And I know what you mean about the auto-scaling being flaky. Sometimes I'll set min/max options that don't make any sense, but the chart will come out looking just right. Then the next time you run it, it will be different. I've learned you just have to experiment, until you get something that seems to work consistently, as best as it can.

              Still, the advances in chart capability in CF7 are light-years ahead of CF5. Everyone at work is VERY pleased with the new charts coming out of CF7. Many thanks again,


              And thanks for the tip about ChartDirector. Sounds nice, but I've got to convince the company to spend $500 for it, plus $99 for the developer license (we'll be running it on Windows XP-Developer, and Windows 2000/2003 Server).

              Again, many thanks for all the tips. Much appreciated.
              • 4. Re: Can You Create Trend Line in CFCHART?
                kodemonki Level 1

                In case anyone is looking, here's something simple I threw together for linear best fit:


                <!--- The min setting is a preference based on how you want the data to be displayed. --->

                <!---<cfset min = arrayMin(grabData["value"]) - 5>--->
                     <cfset min = 0>
                     <cfset max = ceiling(arrayMax(grabData["value"])) + 1>
                     <cfset n = grabData.recordCount>
                     <cfset sum_x = 0>
                     <cfset sum_x2 = 0>
                     <cfset sum_xy = 0>
                     <cfloop from="1" to="#grabData.RecordCount#" index="i">
                          <cfset sum_x = sum_x + i>
                          <cfset sum_xy = (i*grabData.value[i]) + sum_xy>
                          <cfset sum_x2 = sum_x2 + (i*i)>
                     <cfset sum_y = arraySum(grabData["value"])>
                     <cfset slope = round(((n*sum_xy) - (sum_x*sum_y))/((n*sum_x2) - (sum_x*sum_x)))>
                     <cfset intercept = round((sum_y - (slope*sum_x))/n)>
                     <cfset best_fit = QueryNew("id, value")>
                     <cfloop from="1" to="#grabdata.RecordCount#" index="i">
                          <cfset queryAddRow(best_fit)>
                          <cfset value = round((slope*i) + intercept)>
                          <cfset QuerySetCell(best_fit, "id", grabData.xlabel[i])>
                          <cfset QuerySetCell(best_fit, "value", value)>
                     <cfchart scalefrom="#min#" scaleto="#max#" title="#GrabDatapointInfo.label#" format="jpg" chartHeight="450" chartwidth="500" xaxistitle="Month/Year"> 
                          <cfchartseries type="line" query="best_fit" valuecolumn="value" itemcolumn="id"/>
                          <cfchartseries type="line" query="grabData" valuecolumn="value" itemcolumn="xlabel"/>