title: variable value in cfchart
body: <p><em>[UPDATED] Question:</em><br>
  The query that Leigh provided worked, but for some reason, when I replaced the names of the rows and columns to change the query from Cost Per Click to Cost per Registration and Cost per License, I would get different values than expected.</p>
  
  <p><strong>Note</strong>:
  The results I list below are only for the Cost per Registrations, not Cost per Licenses.  Both come from the same table, so if one is fixed, most likely the second one will follow suit.  I also updated the AdReport table to include the Licenses column etc.</p>
  
  <p><em>What I expected</em>  </p>
  
  <pre><code>Date    CPR  
  1      $31.35   
  2      $61.42   
  3      $77.85   
  4      $78.48   
  5      $55.11
  </code></pre>
  
  <p><em>What I got</em>  </p>
  
  <pre><code>Date    CPR  
  1       971.9412  
  2       1781.2939  
  3       2421.733  
  4       2355.4679  
  5       1598.164  
  </code></pre>
  
  <p><strong>Queries:</strong></p>
  
  <pre><code> &lt;cfquery name="costPerRegistration" datasource="#dsn#"&gt;
  SELECT ab.AdMonth AS Date, 
      CASE WHEN SUM(ar.Conversions) &gt; 0 THEN SUM(ab.AdBudget) / SUM(ar.Conversions)
              ELSE 0
              END AS CPR
  FROM AdBudget AS ab INNER JOIN AdReport AS ar
        ON  DATEPART(MONTH, ar.ReportDate) = ab.AdMonth
        AND DATEPART(YEAR, ar.ReportDate)  =  ab.AdYear
        AND ar.AdSourceID = ab.AdSourceID
      WHERE ab.AdYear = '2016' 
      AND ar.AdSourceID != 4
      GROUP BY ab.AdMonth
      ORDER BY ab.AdMonth
  &lt;/cfquery&gt;
  
  &lt;cfquery name="costPerLic" datasource="#dsn#"&gt;
  SELECT  ab.AdMonth AS Date, 
      CASE WHEN SUM(al.Licenses) &gt; 0 THEN CAST(SUM(ab.AdBudget)/SUM(al.Licenses) AS smallmoney)
              ELSE 0
              END AS CPL
  FROM AdBudget AS ab INNER JOIN AdReport AS al 
        ON  DATEPART(MONTH,al.ReportDate) = ab.AdMonth 
        AND DATEPART(YEAR,al.ReportDate) = ab.AdYear  
        AND al.AdSourceID = ab.AdSourceID
  WHERE   ab.AdYear = 2016 
  AND     ab.AdSourceID != 4
  GROUP BY ab.AdMonth
  ORDER BY ab.AdMonth
  &lt;/cfquery&gt;
  </code></pre>
  
  <p><strong>Code:</strong>    </p>
  
  <pre><code>&lt;cfloop index = "i" from = "1" to = "#AdBudget.RecordCount#"&gt; 
      &lt;cfset Clicks.Click[i] = AdBudget.Budgeting/Clicks.Click[i]&gt; 
      &lt;cfset Registrations.Conver[i] = AdBudget.Budgeting/Registrations.Conver[i]&gt;
      &lt;cfset Licenses.License[i] = AdBudget.Budgeting/Licenses.License[i]&gt; 
  &lt;/cfloop&gt; 
  
  &lt;!--- Bar graph, from Query of Queries ---&gt;
  &lt;cfchart&gt; 
      &lt;cfchartseries type="curve" 
          seriescolor="##5283DA" 
          serieslabel="Cost per Clicks"
            &lt;cfchartdata item="1" value="#Click#"&gt;
      &lt;/cfchart&gt;
  &lt;/cfchart&gt;
  </code></pre>
  
  <p><strong>Data:</strong></p>
  
  <p>Sample Data added, disregard the sourceID and other IDs in the table.  </p>
  
  <pre><code>AdBudgetID  AdBudget    AdMonth AdSourceID  AdYear
      1   7663    1   1   2016
      2   20301   2   1   2016
      3   5555    1   2   2016
      4   16442   2   2   2016
      5   1706    1   3   2016
      6   4841    2   3   2016
      7   11384   3   1   2016
      8   23726   3   2   2016
      9   9653    3   3   2016
      13  17557.98    5   1   2016
      14  25685.72    5   2   2016'
  
  AdClickID   AdClicks    AdMonth AdSourceID  AdYear
  1   2229    1   1   2016
  2   1803    1   2   2016
  3   371 1   3   2016
  4   4940    2   1   2016
  5   5855    2   2   2016
  6   673 2   3   2016
  7   2374    3   1   2016
  8   12913   3   2   2016
  9   1400    3   3   2016
  13  2374    4   1   2016
  14  10272   4   2   2016
  
     AdReportID   ReportDate  AdSourceID  Clicks  Conversions Demos   Clients    Licenses Onboardings AvgScore
  2430    2016-03-27  1   1   1   0   0   0   0   NULL
  2431    2016-03-27  2   5   0   0   0   0   0   NULL
  2432    2016-03-27  3   1   0   0   0   0   0   NULL
  2433    2016-03-27  5   24  0   0   0   0   0   NULL
  2434    2016-03-27  6   0   0   0   0   0   0   NULL
  2435    2016-03-27  6   0   0   0   0   0   NULL    NULL
  2436    2016-03-27  4   0   1   0   0   0   1   NULL
  2437    2016-03-26  1   2   0   0   0   0   0   NULL
  </code></pre>
  
  <p>Sorry about the table config, not sure how to make it neat.  Also, we have a lot more conversions(registrations) and licenses that are not shown in the sample data, it just happened that the first ~10 rows had low numbers. </p>