4 Replies Latest reply on Jan 25, 2011 10:44 AM by philmodjunk

    Displaying Summary Field data in sub-summary

    DennisPrickett

      Title

      Displaying Summary Field data in sub-summary

      Post

      I have a data set of test scores, Test1 and Test2. Test 1 is given at the beginning of the semester, and Test2 and the end.  I need to calculate an average for each for a group of students - by grade or classroom, and calculate the change in the average scores.  I initially used a summary field, Test1_Summary and Test2_Summary to calculate the average of each, and then another field to calculate the difference between the two summary fields.  I found that the averages were wrong because the Summary field was including the students who had no score entered, which brought the average down.  I then changed the summary fields to total the test scores, and used another calculation field, Test1_Count and Test2_Count to count the number of students who took the test, then used Test1_Summary/Test1_Count to calculate the average.

      This worked fine until I laid it out using a sub-summary sorted by grade.  If I search for a single grade, it displays fine, as shown on the screenshot below.  If I include all grades or multiple grades, and sort by grade, the average summary field is correct for each grade (though technically wrong because of empty fields), but Test1_Summary/Test1_Count and  Test2_Summary/Test2_Count shows the same value on each line of the subsummary.

      Aren't summary fields evaluated within a subset, as defined in the sort restriction of a sub-summary?

      sample.JPG

        • 1. Re: Displaying Summary Field data in sub-summary
          philmodjunk

          "I found that the averages were wrong because the Summary field was including the students who had no score entered, which brought the average down."

          Better check that again. A summary field set to compute the average of a field will not include records where the summarized field is empty.

          [2]
          [2]
          [empty]

          Will compute an average of 2, not 1.333...

          Summary fields DO compute sub-totals, but only when it's placed inside a sub summary and sorted by the sub summary's break field. If you use a summary field inside a calculation, it computes a grand total instead of returning the sub total. There's function, however, that you can use to get sub totals from a summary field.

          GetSummary ( SummaryField ; BreakField ) will return a sub total if the current found set is sorted by BreakField. Thus, you can use this function in a calculation to compute a value based on one or more sub totals.

          • 2. Re: Displaying Summary Field data in sub-summary
            DennisPrickett

            For some reaon I convinced myself that a summary field set to compute the average of the field didn't work for me, but it checking it now it does.  Thanks.

            • 3. Re: Displaying Summary Field data in sub-summary
              BG_1

              I'm having a similar (sort-of) problem.  I thought using a report would be great, but I need to report the difference of each question field from pre-test to post-test.  Unfortunately, it seems that reports are restriced to very certain calculations (i.e. I cannot take the difference).  Is there any way to bypass this, or should I just use calculations?

              • 4. Re: Displaying Summary Field data in sub-summary
                philmodjunk

                B G,

                You'll need to explain what you are trying to do and how you've structured your database before someone can offer some help. You can certainly take the difference between values in two or more records, but the details depend on what you've set up and what you want to do.