Coldfusion Charting Multiple Series from QoQ

Question

I am trying to insert a chart in an email that will graph multiple series' data over time. The email works fine, the chart attaches successfully but something is wrong with how I am generating the chart.

What I am attempting to do: Loop through the query results, group by the 'territory description' (to make a series for each territory), then generate a data point for each territory for each month.

Here is a sample of the Query of Queries results that I am attempting to graph:

There is an entry for each of six 'territories' for each of six months.

Here is the code I found online that I've been trying to work with.

 <cfchart
        format="png"
        name="marketADH"
        xAxisTitle="Month"
        yAxisTitle="% Adherence"
        chartHeight = "500"    
        chartWidth = "500" 
        scalefrom="60"
        scaleto="90"
        font="Arial"
        gridlines="10"
        showXGridlines="no"
        showYGridlines="no"
        showborder="no"
        show3d="no" 
        showlegend="yes"
        sortxaxis="no"
        showmarkers="yes" 
 > 



 <cfloop query="HISTORY_CHART" group="TERRITORY_DESC"> 
      <!---- this part of the code will run ONCE for each territory ---->

   <cfchartseries 
     type="curve"
     paintStyle="plain"
     seriesLabel="#HISTORY_CHART.TERRITORY_DESC#">

     <cfloop>
         <!----- this part of the code will run once for every record for the territory  ------>
         <cfchartdata item="#HISTORY_CHART.RPT_MTH#" value="#HISTORY_CHART.ADHERENCE_PCT#">
     </cfloop>

   </cfchartseries>

  </cfloop>

 </cfchart>

Results: The chart is generating and attaching in the email, but there is only one month value - 12/01/2017 which is the first of the six months in the data set. All 36 or so values appear to be populating on the y-axis for this month.

What I expected to see was something like this:

What I ended up with was this:

Any help anyone can provide would be greatly appreciated. Thanks so much.

SOS SOS · Accepted Answer · 2018-06-03T00:50:59

<cfloop query="HISTORY_CHART" group="TERRITORY_DESC">

I'm guessing the "group" isn't working as expected because the query data isn't sorted correctly.

What the cfloop documentation doesn't emphasize nearly enough is that the "group" feature requires query data be sorted in the same order as the "group" statement(s). If it's sorted by a different column (say "Rpt_Mth") and cfloop is grouped by "Territory_Desc", you won't get the expected output.

Sorted vs Unsorted example on TryCF.com

Make sure the sql query sorts by the same column used in the group, "Territory_Desc". Then the chart should generate correctly.

<cfquery ...>
  SELECT Adherence_Pct,Rpt_Mth,Territory_Desc 
  FROM   TableName 
  ORDER BY Territory_Desc 
</cfquery>

Coldfusion Charting Multiple Series from QoQ

1 Answers