2 Replies Latest reply on Nov 30, 2011 12:37 PM by JoshuaKanuch

    Calculation Field Average of Found Values



      Calculation Field Average of Found Values


      I have a database for school assignments and I am stuck on the following issue:

      I have a table of Assignments where each student is given a grade of 0, 1, 2, or 3. Each assignment is also given a category, such as "Understand", "Apply", "Create", etc. There is also a table of Skills, where each Student gets a final assessment on that particular Skill. For every Skill, there are multiple Assignments, and each Assignment having only 1 Category.

      What I want to do is have a report that averages a Student's score for each Category. For instance, Joe Smith is assigned Science Skill One. For that skill there are 3 "Apply" Assignments, and 2 "Understand" Assignments. I want to see what Joe Smith's average is for Apply, and for Understand.

      I have fields set up in a related "Report Table" to calculate the average for each of these Categories, yet I am having trouble with the calculation. I know I'm missing a step to filter the values. Below is an example of my calculation:

      SkillCateg_Understand =

      If ( Assignment Registration::Category = "Understand" ; Average ( Assignment Registration::Mastery Level; ) )

        • 1. Re: Calculation Field Average of Found Values

          This is a common mistake made with aggregate functions such as sum and average. These functions cannot be set up to be used selectively. They sum or average all related values as controlled by the defined relationship.

          As you have written it, the IF function controls whether or not the average function evaluates, not which values in the related table will be averaged.

          If the first related record in Assignment Registration::category = "Understand", your average function will average the Mastery Level field for all related recordsin Assignment Registration, not just those in the "understand" category. If the first related record isn't in the understand category, you don't get any value returned.

          A summary report based on the Assignment Registration table can be set up where you group records by student, then by category by sorting the records, then a summary field can be used to compute the average for each category.

          • 2. Re: Calculation Field Average of Found Values

            To add, I have tried to create another calculated field using the "List" function. It did succeed in listing only the values that I needed to average, yet I am unsure how to average all of the values in a return-separated list to return one value. Instead, it returns separate averages for all of the values in the list. I believe this is on the right track, but I am not sure how to achieve one average from a return-separated list field.