1 Reply Latest reply on May 13, 2015 7:10 AM by philmodjunk

    Weekly Average via a related portal

    DavidTinoco

      Title

      Weekly Average via a related portal

      Post

      I have 2 tables that I want to work with to get a weekly average.

      The first table is students.

      The second table contains their work hours.

      The work hours table has these fields:

      Student ID
      Weekending
      Date
      Hours

      An example entry would be:

      1
      5/12/15
      5/9/15
      2:18

      Each week ends on a Thursday, so the Weekending field will always be a Thursday.

      What I would like to achieve is a field on my students layout, that always gives the average hours worked by Weekending week, for each student. Note that this would not be a report with sorted data, but simply a calculation field that got the average hours worked by week in a relation.

      Is there a way to do this? The issue I run into is the sorting by weekending when it's a calculation. I don't know if it can be done.

        • 1. Re: Weekly Average via a related portal
          philmodjunk

          Make sure that your result type for week ending is date and not some other value as this will make for better sorting.

          You can set up a summary report with a sub summary part sorted by Student ID. define summary field that computes the total of Hours and put it in this sub summary part. Sort your records first by weekending, then by student ID to group your records first by week, then by student.

          If you remove the body layout part, you can get a list of one such sub total per student per week.

          There are also other ways to compute this total from the context of a related table where you have one record for each student. This can sometimes be a better way if you need to list students that worked 0 hours.