?
Solved

Averaging the total score for a multi-question evaluation...

Posted on 2006-03-30
22
Medium Priority
?
465 Views
Last Modified: 2013-12-24
I have a evaluation form that collects a score (1 - 10) and some comments.
Several people will evaluate one person, in turn submitting a form for each evaluation.
I need to average the scores for each question from all the Evaluators and
display the total average and each comment from all the evaluators in one result record
for each question.

EG:

Eval1            Eval2           Eval3          Eval4
5                  4                 7               6
Comment1    Comment2   Comment3  Comment4


Results
Overall Eval
5.5
Comment1
Comment2
Comment3
Comment4
0
Comment
Question by:jollymon6672
  • 8
  • 5
  • 4
  • +1
19 Comments
 
LVL 12

Expert Comment

by:mkishline
ID: 16333680
<cfquery name="queryname" datasource="dbname">
  SELECT grade, comment, person
  FROM tablename
  WHERE person = "criteria"
</cfquery>

<cfoutput query="queryname" group="person">
      #ArrayAvg(ListToArray(ValueList(queryname.grade)))#
      <cfoutput group="headline">
      #comment#
      </cfoutput>
</cfoutput>
0
 
LVL 7

Expert Comment

by:JeffHowden
ID: 16334855
Alternatively, make your database engine do what it's good at (the math)

<cfquery name="queryname" datasource="dsn">
  SELECT (eval1 + eval2 + eval3 + eval4) / 4 AS avg_grade
            , comment
            , person
    FROM tablename
</cfquery>

This will work if every record has an evaluation.  However, if some records only have none, 1, 2, or 3 evaluations, that'll throw the entire thing off and will require a much more complicated solution than either I or mkishline has suggested.
0
 

Author Comment

by:jollymon6672
ID: 16335009
Does this measn I need a query for each question in the eval or can I loop through the eval from all submitters and generate the total evaluation?
0
[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

 
LVL 12

Expert Comment

by:mkishline
ID: 16335332
Can you describe how your database is set up? That may help abet some of the confusion.
0
 
LVL 7

Expert Comment

by:JeffHowden
ID: 16337025
Also, please indicate whether or not every question will have a non-NULL/non-zero value for eval1, eval2, eval3, and eval4.
0
 
LVL 9

Expert Comment

by:digicidal
ID: 16342905
Assuming that your evaluation field is an INT value and is required, then no you would simply be able to loop through the query as indicated in mkishline's example.  Using ArrayAvg() in his example will work just fine, or if you would like to have a long-hand version you could use the following:

<cfquery name="queryname" datasource="dbname">
  SELECT grade, comment, person
  FROM tablename
  WHERE person = "criteria"
</cfquery>
<cfset GradeTotals = 0>
<cfoutput query="queryname">
Eval#queryname.CurrentRow# #grade# #comment#<br>
<cfset GradeTotals = GradeTotals + grade>
</cfoutput>
<cfoutput>
Results (Overall Average): #Evaluate(GradeTotals/queryname.RecordCount)#
</cfoutput>
0
 
LVL 7

Expert Comment

by:JeffHowden
ID: 16347785
The database would be the more appropriate place to do the mathematical calculations as it's much faster at them than CF.

Also, it'd be more appropriate in the following snippet "#Evaluate(GradeTotals/queryname.RecordCount)#" to use the Val() function instead of the Evaluate() function:

#Val(GradeTotals / queryname.RecordCount)#

You should only ever use the Evaluate() function in extremely rare circumstances.
0
 
LVL 9

Expert Comment

by:digicidal
ID: 16351197
Really?  According to my CF manuals the Val() function is for converting numeric characters at the beginning of a string into a number, while Evaluate() is stated indicated for evaluating expression(s).  I never knew that you could use Val() in that way - and Macromedia doesn't indicate that you can either.

Like most things in CF... it figures out what you wanted (most of the time accurately)... consider the following:

<cfscript>
y = 200;
z = 3;
</cfscript>
<cfoutput>VAL: #Val(y/z)# | EVALUATE: #Evaluate(y/z)# | NONE: #y/z# | FORMAT: #NumberFormat(y/z,'999.999')#</cfoutput>

With the exception of the last item (which because of the mask is rounded to only 3 decimal places - they all return the same thing (if you replaced the mask with '999.9999999999' then they would all be exactly the same.

I have to disagree with Jeff about using Val as in many discussions I've had with other developers the same could be said about it...  "You should only ever use the Val() function in extremely rare circumstances."  However, he is right in Evaluate - being a function - having drawbacks.  If the absolutely tighest code possible is desired... then use #GradeTotals/queryname.RecordCount# with no function call whatsoever.  It evaluates just fine without one - or just <cfset variable = GradeTotals/queryname.RecordCount> and then output #variable#.  Many possible solutions and all work.

Cheers.
0
 
LVL 7

Expert Comment

by:JeffHowden
ID: 16352048
Digicial, I realize you are just going off the description for the Val() function from Macromedia's ColdFusion language reference.  That's fine to do, however, it  is considered best practice by developers considered to be experts in the CF community to use the Val() function to ensure the data you're working with is numeric.  Additionally, most, if not all, of these same expert developers will strongly discourage the use of the Evaluate() function except when all other options have been exhausted.

To the OP though, for the very tightest code possible, make sure to enforce a number for GradeTotals and a non-zero recordcount lest errors be thrown in circumstances where GradeTotals isn't a number or recordcount results in a divide by zero error:

<cfscript>
  avg = 0;
  if(queryname.recordcount)
    avg = Val(GradeTotals) / queryname.recordcount;
</cfscript>
<cfoutput>#avg#</cfoutput>

Be *very* careful of using #somevar + someothervar# as it may give you unintended results.  For example, if you end up having to put the code on a pre-CF5 box, that particular snippet of code will throw errors.  Granted, there aren't many pre-CF5 boxes in production, but better safe than sorry.  Additionally, if you don't ensure that "somevar" and "someothervar" are both numeric, attempting an arithmetic operation will result in an error.

0
 
LVL 9

Expert Comment

by:digicidal
ID: 16355329
Well, I wholeheartedly agree with you as far as Evaluate() not being the best solution - I was in error using it (it's an old habit unfortunately from when I just moved to CF from working in a couple other languages where it would have been used in that way).  And I do agree with you on Val() being better as far as using a function to return a number.  I've however spoken to several experts in CF (two of whom worked on MX - writing it... not developing in it) and they indicated several reasons that it would not optimize as well in the compiler due to the way the string conversion functions in Java operate.  I have to take their word on this, however I have run into problems with it myself due to it not being able to handle commas or exponential notation for numbers (which granted, are not that common, unless say you're using Excel as a COM object).

In any case I completely agree with your solution.  I would even adjust the check for RecordCount to occur prior to any of the output, so that if queryname.recordcount EQ 0 then the entire output is skipped and a more user friendly message is displayed.  

This would be fastest to process anyway.  Also according to a discussion with a friend that is a big J2EE (pure java) programmer - who doesn't know CF but I showed him the function options, recommended that in his opinion (again based on how he'd write it in java) the best option would probably be to use "IsNumeric()" to check for a number and based on that boolean value compute or escape the calculation function.  Again you're way works great - just some more food for thought.

For this question I guess that would give us (assuming that you didn't already check for recordcount elsewhere:
<cfscript>
  avg = 0;
  if(queryname.recordcount AND IsNumeric(GradeTotals))
    avg = GradeTotals / queryname.recordcount;
</cfscript>
<cfoutput>#avg#</cfoutput>
0
 

Author Comment

by:jollymon6672
ID: 16395428
WOW, I never thought my question would produce such an indepth conversation on best practice. It is great to know that there are so many "Experts" out there.

To answer so previous question:

My DB table (Access 2003) is as follows:

There are 18 questions total, several people will be evaluating a individual, each one has:

1. A numeric value of 1 -10
2. A Comment field

As mentioned I am trying to query the table, return the values and the average score per question, while also returning all comments for each question from all evaluators.

So the results should be:

Question 1: How well does this player communicate
Average score: 7.0
Comments:
Eval1: Does a good job
Eval2: What a talker
Eval3: Never shuts up
Eval4: Learn english

It is not a high traffic site so I only need Access for the DB.
0
 
LVL 9

Accepted Solution

by:
digicidal earned 500 total points
ID: 16396441
Here is one solution that will work I think... give it a try (after changing the names in the queries to the actual fields, datasource name, and the personvalue variable of course!  Note: You should be able to do this with an inline calculation and grouping on the query itself, but I'm not sure of Access syntax for this... so here is one solution using a query of queries function.  I'm sure I've made a mistake here since I've been up for way too many hours... but give this a shot.

<cfquery name="questionQuery" datasource="myDatasource">
SELECT QuestionNumber, QuestionText FROM questiontable ORDER BY QuestionNumber ASC
</cfquery>
<cfquery name="scoreQuery" datasource="myDatasource">
SELECT QuestionNumber, Score, Comment FROM scorestable WHERE PersonReviewed = <cfqueryparam value="#personvalue#">
</cfquery>
<cfloop query="questionQuery">
    <cfquery name="tempQuery" dbtype="query">
      SELECT * FROM scoreQuery WHERE QuestionNumber=#questionQuery.QuestionNumber#
    </cfquery>
      <!--- Now we have all the query results from our question so we'll pre-process --->
      <cfscript>
      procError = FALSE; // In case we get errors so we can 'crash' softly.
      if (tempQry.Recordcount) {
            // First we'll zero out our variables.
          scoreTotal = 0;
            avgScore = 0;
            // Now loop over our temp query and calculate our total.
            For (i=1;i LTE tempQry.Recordcount; i=i+1) {
                  tempScoreTotal = tempScoreTotal + val(tempQry.Score[i]);
            }
            // Now calculate average from our culled values for this question.
            avgScore = scoreTotal/tempQry.Recordcount;
            } else {
            // ERROR - Prevents Divide By Zero
            procError = TRUE;
      }
      </cfscript>
      <cfif procError>
            <b>No scores were found for Question #questionQuery.QuestionNumber#</b><br /><br />
            <cfelse>
                  <cfoutput>
                  <hr />
                  Question #questionQuery.QuestionNumber#: #questionQuery.QuestionText#<br />
                  Average Score: #NumberFormat(avgScore,"9999.999")#<br />
                  Comments:<br />
                        <cfloop query="tempQuery">
                        Eval#tempQuery.CurrentRow#: #tempQuery.Comment#<br />
                        </cfloop>
                  <hr />
                  </cfoutput>
      </cfif>
<cfloop>

0
 
LVL 7

Assisted Solution

by:JeffHowden
JeffHowden earned 500 total points
ID: 16396452
Since you need to retrieve the comments from each evaluator, you really won't be able to query the average at the DB level.  So, that leaves constructing the query for the entire resultset and then some logic for determining the average score.  I realize that each score has a value from 1-10, however, the extra logic will allow you to permit some questions to be evaluated by less than 4 evaluators and the average score will still be accurate.

<cfquery name="queryname" datasource="dbname">
  SELECT question, eval1, comment1, eval2, comment2, eval3, comment3, eval4, comment4
    FROM tablename
  WHERE person = "criteria"
</cfquery>

<cfloop query="queryname">
  <cfscript>
    evaluators = 0;
    total_score = 0;
    avg_score = 0;
    for(i = 1; i LTE 4; i = i + 1)
    {
      if(Len(Trim(queryname['eval' + i][currentrow])))
      {
        evaluators = evaluators + 1;
        total_score = total_score + Val(queryname['eval' + i][currentrow]);
      }
    }
    avg_score = total_score / evaluators;
  </cfscript>
  <cfoutput>
    Question #currentrow#: #question#<br />
    Average Score: #DecimalFormat(avg_score)#<br />
    Comments:<br />
    Eval1: #comment1#<br />
    Eval2: #comment2#<br />
    Eval3: #comment3#<br />
    Eval4: #comment4#<br />
    <br />
  </cfoutput>
</cfloop>
0
 

Author Comment

by:jollymon6672
ID: 16547951
Jeff,

What you appear to be proposing suggests that the questions are also stored in the DB table. So, I would have to loop over them for the initial rendering of the evaluation form. Correct?
0
 
LVL 7

Expert Comment

by:JeffHowden
ID: 16548977
You mean for those scoring the questions?
0
 

Author Comment

by:jollymon6672
ID: 16549742
Currently my table only holds the Evalualtor ID, The question rating and the comments.
0
 
LVL 7

Expert Comment

by:JeffHowden
ID: 16555099
Where are the questions stored then?  How do you know what question a record in this table is for?
0
 

Author Comment

by:jollymon6672
ID: 16564354
The questions were coded in the HTML. Sounds like I need to store them in the DB. Suggestions on the best method to organize the table?
0
 
LVL 7

Expert Comment

by:JeffHowden
ID: 16564431
I think that goes beyond the scope of your original question.  Probably best to close this one out, marking the expert's response that helped you the most, and opening a new question about database structure with a link to this question for background.
0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

One of the typical problems I have experienced is when you have to move a web server from one hosting site to another. You normally prepare all on the new host, transfer the site, change DNS and cross your fingers hoping all will be ok on new server…
When it comes to showing a 404 error page to your visitors, you do not want that generic page to show, and you especially do not want your hosting provider’s ad error page to show either. In this article, I will show you how to enable the custom 40…
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…
Whether it be Exchange Server Crash Issues, Dirty Shutdown Errors or Failed to mount error, Stellar Phoenix Mailbox Exchange Recovery has always got your back. With the help of its easy to understand user interface and 3 simple steps recovery proced…
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question