variable value in cfchart

Question

[UPDATED] Question:
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.

Note: 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.

What I expected

Date    CPR  
1      $31.35   
2      $61.42   
3      $77.85   
4      $78.48   
5      $55.11

What I got

Date    CPR  
1       971.9412  
2       1781.2939  
3       2421.733  
4       2355.4679  
5       1598.164

Queries:

 <cfquery name="costPerRegistration" datasource="#dsn#">
SELECT ab.AdMonth AS Date, 
    CASE WHEN SUM(ar.Conversions) > 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
</cfquery>

<cfquery name="costPerLic" datasource="#dsn#">
SELECT  ab.AdMonth AS Date, 
    CASE WHEN SUM(al.Licenses) > 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
</cfquery>

Code:

<cfloop index = "i" from = "1" to = "#AdBudget.RecordCount#"> 
    <cfset Clicks.Click[i] = AdBudget.Budgeting/Clicks.Click[i]> 
    <cfset Registrations.Conver[i] = AdBudget.Budgeting/Registrations.Conver[i]>
    <cfset Licenses.License[i] = AdBudget.Budgeting/Licenses.License[i]> 
</cfloop> 

<!--- Bar graph, from Query of Queries --->
<cfchart> 
    <cfchartseries type="curve" 
        seriescolor="##5283DA" 
        serieslabel="Cost per Clicks"
          <cfchartdata item="1" value="#Click#">
    </cfchart>
</cfchart>

Data:

Sample Data added, disregard the sourceID and other IDs in the table.

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

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.

... so if you are trying to create a data point, for each element in the array, you need to loop through it, and create a data point on each element. Side note 1) The cfloop may not be doing what you think it is. It will always use the `AdBudget.Budgeting` value in the first row of the query. 2) Get rid of the `Evaulate()`. In this context, it does nothing. — Leigh, Jun 11 '16 at 00:24
I realize that Dan, I do not know how to cfloop an array, and I thought that this would work, I just do not know how to call a variable from the cfloop to the chart. — N. Ziff, Jun 12 '16 at 15:27
Leigh, I'm not sure that I understand what you mean by it will always use the 'AdBudget.Budgeting' vaue in the first row..., but thanks for the tip on the evaluate() function! — N. Ziff, Jun 12 '16 at 15:30
(Edit) Unless you are inside a query loop, `AdBudget.Budgeting` is a shortcut for *"use the value in the first row of the query"*. BTW, opening multiple threads for [the same question](http://stackoverflow.com/questions/37794107/charting-variables-from-cfloop) is [discouraged](http://stackoverflow.com/help/closed-questions). The preferred method is to stick with one thread per question. [Edit the question](http://stackoverflow.com/posts/37794107/edit) to include more details if needed. Be sure to include a description of the actual result - and how it differs from what you expected. — Leigh, Jun 13 '16 at 19:01
@Leigh Thanks for the tip. I thought that using 'from = "1" to = "#AdBudget.RecordCount#"' would change the row by AdMonth, changing the AdBudget value. How would you go about changing it? — N. Ziff, Jun 14 '16 at 13:23
Since we do not have access to your database, it is not clear what you mean by *there are some problems with it*. What is the code actually doing? What did you expect it to do instead? — Leigh, Jun 14 '16 at 14:32
(Edit-typos) @N.Ziff - Nope. A only a query loop, ie `` automatically advances to the next row. Inside it, `TheQueryName.ColumnName` would always contain the current value. However, a `from/to` loop does nothing except loop a certain number of times and populate the `index` variable with the loop number. What you do with that index is left up to you ;-) To access a specific row in a query use associative array notation, ie `queryName[ "stringColumnName" ][ rowNumber ]`. — Leigh, Jun 14 '16 at 14:35
@Leigh When I said _there are some problems with it_ I meant it only as a segway into the question. Thanks for explaining the difference between looping a query and what I'm looping, I am starting to understand it more. Would I use your answer on my other question and loop that query to get my desired result? — N. Ziff, Jun 14 '16 at 15:07
That still does not explain the exact problem you are having. Remember, we cannot run your code, or see the results, so without a crystal ball we can't know what it is doing wrong unless you tell us ;-) I really think a loop is the wrong approach, for a few reasons. In order to cut to the chase faster, please update your post with a) structure of the three tables ie column names and data types and B) a small sample of the data? — Leigh, Jun 14 '16 at 15:43
(Edit) Sorry, just noticed the data sample at the end of the code. Can you add a sample of AdReport too, with the raw date values? Also, do all three (3) tables *always* contain an entry for every month and year? — Leigh, Jun 14 '16 at 16:25
@Leigh Yes, all 3 tables always contain an entry for month and year. I will add the AdReport sample too. — N. Ziff, Jun 14 '16 at 17:01

Leigh · Accepted Answer · 2016-06-15T18:40:31.520

how to set the chart up to chart them correctly

I would actually start with that question, rather than the queries. Start with a small hard coded chart, that is easy to adjust, in order to figure out exactly how the tags should be constructed to produce the desired chart. The final groupings will determine how you should aggregate the query data.

Say you want to chart the "Cost per click" and "Cost per Registration", for January to May of 2016, as separate series. Creating a hard coded example like this:

<cfchart> 
    <cfchartseries type="curve" serieslabel="Cost per Clicks">
       <cfchartdata item="1" value="15">
       <cfchartdata item="2" value="50">
       <cfchartdata item="3" value="47">
       <cfchartdata item="4" value="32">
       <cfchartdata item="5" value="65">
    </cfchartseries>
    <cfchartseries type="curve" serieslabel="Cost per Registration">
       <cfchartdata item="1" value="45.52">
       <cfchartdata item="2" value="17.68">
       <cfchartdata item="3" value="28.50">
       <cfchartdata item="4" value="78.62">
       <cfchartdata item="5" value="42.50">
    </cfchartseries>
</cfchart>

Produces this chart:

Looking at the cfchartdata tags, indicates two queries are needed: one containing the "Cost per Click" by month, and another the "Cost per Registration" by month.

To calculate the cost per click, join AdBudget and AdClick on the month, year and source id columns. Group the results by month, and divide the total budget by the total clicks:

SQLFiddle Example:

<cfquery name="costPerClick" ....>
SELECT   ab.AdMonth
         , CASE WHEN SUM(ac.AdClicks) > 0 THEN SUM(ab.AdBudget) / SUM(ac.AdClicks)
                ELSE 0
           END AS CostPerClick
FROM    AdBudget ab LEFT JOIN AdClick ac 
          ON  ac.AdMonth = ab.AdMonth 
          AND ac.AdYear = ab.AdYear  
          AND ac.AdSourceID = ab.AdSourceID
WHERE   ab.AdYear = 2016 
AND     ab.AdSourceID <> 4
GROUP BY ab.AdMonth
ORDER BY ab.AdMonth
</cfquery>

Then simply loop through the query to generate the cfchartseries:

<cfchart> 
    <cfchartseries type="curve" serieslabel="Cost per Clicks">
       <cfoutput query="costPerClick">
          <cfchartdata item="#costPerClick.AdMonth#" 
               value="#DecimalFormat(costPerClick.Amount)#">
       </cfoutput>
    </cfchartseries>
</cfchart>

Using the hints above, you should be able to follow the same process to generate the other two queries and series.

Update:

Turns out I misunderstood the structure of AdReport. Since it contains multiple records for each month/year/sourceID combination, you will need a calculate the total conversions by month first. Then JOIN that result back to AdBudget. So the "Cost per Registration" query would need to be something like this:

SELECT  ab.AdMonth 
        , CASE WHEN SUM(ar.Conversions) > 0 THEN SUM(ab.AdBudget) / SUM(ar.Conversions)
               ELSE 0
          END AS CPR
FROM    AdBudget ab LEFT JOIN 
        (
            /* Calculate total conversions per month */
            SELECT AdSourceID
                    , DATEPART(MONTH, ReportDate) AS AdMonth
                    , DATEPART(YEAR, ReportDate) AS AdYear
                    , SUM(Conversions) AS Conversions
            FROM   AdReport
            --- first of desired year (2016) to first of next year (exclusive)
            WHERE  ReportDate >= '2016-01-01'
            AND    ReportDate < '2017-01-01'
            AND    AdSourceID <> 4
            GROUP BY AdSourceID
                     , DATEPART(MONTH, ReportDate)
                     ,  DATEPART(YEAR, ReportDate)
        ) 
        ar  ON  ar.AdMonth = ab.AdMonth 
            AND ar.AdYear = ab.AdYear  
            AND ar.AdSourceID = ab.AdSourceID           
GROUP BY ab.AdMonth
ORDER BY ab.AdMonth

variable value in cfchart

1 Answers1