Monitor Reply

Support

Calculate average of a specific field

Permalink Browser Info Environment September 05, 2012 at 11:45 AM

Hi,

I was wondering if there is a known way to calculate the average of all the answers to a specific question within a form and display it through Data Display.
Use case: A form can be filled and you are, among others, asked to rate a service on a scale from 1 to 5. I'd like to be able to not only list all the records and a specific record but also the aggregate rating people submitted through the form.

I know I might have to modify the core of the package or add a function myself, but if anyone already has done something similar or if I have overlooked a built in function that could help me here I'd be very happy to be pointed to it.

Thanks!

Type:	Discussion
Status:	New

mckoenig replied on Sep 6, 2012 at 5:39 pm Permalink Reply

If anyone is interested, I created this function in the blocks/sixeightdatadisplay/controller.php to solve my problem:

function calculateAverageRating($fullAnswerSet, $match) {
      $ratingSum = 0;
      $numberOfRatings = 0;
      $formID = $fullAnswerSet[0]->fID;
      $db = Loader::db();
      $fieldID = $db->getRow("SELECT ffID FROM sixeightformsFields WHERE fID = ? AND label = ?", array($formID, $match));
      if(count($fieldID) > 0){
         $fieldID = $fieldID['ffID'];
      } else {
         return false;
      }
      $approvedAnswerSets = $db->Execute("SELECT asID FROM sixeightformsAnswerSets WHERE fID = ? AND isApproved = 1 AND isDeleted = 0", array($formID));
      $counter = 0;
      foreach($approvedAnswerSets as $answerSet){
         $rating = $db->getRow("SELECT value FROM sixeightformsAnswers WHERE asID = ? AND ffID = ?", array($answerSet['asID'], $fieldID));

Viewing 15 lines of 29 lines. View entire code block.

 
function calculateAverageRating($fullAnswerSet, $match) {
 
      $ratingSum = 0;
      $numberOfRatings = 0;
 
      $formID = $fullAnswerSet[0]->fID;
      $db = Loader::db();
      $fieldID = $db->getRow("SELECT ffID FROM sixeightformsFields WHERE fID = ? AND label = ?", array($formID, $match));
      if(count($fieldID) > 0){
         $fieldID = $fieldID['ffID'];
      } else {
         return false;
      }
 
 
      $approvedAnswerSets = $db->Execute("SELECT asID FROM sixeightformsAnswerSets WHERE fID = ? AND isApproved = 1 AND isDeleted = 0", array($formID));
      $counter = 0;
      foreach($approvedAnswerSets as $answerSet){
         $rating = $db->getRow("SELECT value FROM sixeightformsAnswers WHERE asID = ? AND ffID = ?", array($answerSet['asID'], $fieldID));
         if(is_numeric($rating['value'])){
            $ratingArray[$counter] = $rating['value'];
            $counter++;
         }         
      }      
      $ratingSum = array_sum($ratingArray);
      $numberOfRatings = count($ratingArray);
 
      if($numberOfRatings > 0 && $ratingSum > 0){
         $averageRating = $ratingSum / $numberOfRatings;
      }else{
         $averageRating = 0;
      }            
      return $averageRating;
   }

Should be pretty self explanatory. If questions arise, feel free to reply or sent me a message.

Data Display

Support

Calculate average of a specific field

Issue Reply

concrete5 Environment Information

Browser User-Agent String

Hide Post Content

Request Refund

Code

Delete Post