1 2 3 Previous Next 38 Replies Latest reply on Jun 24, 2009 7:53 PM by Pine

    Can't Determine Average Based on a Summary Field

    Pine

      Title

      Can't Determine Average Based on a Summary Field

      Post

      Hi,
       
      I have a funding table in which a student can receive *multiple* grants over time. (So each student in the student table can have multiple grant records in the funding table; each grant has a transaction id # [pk] in the funding table). When I total the grants per student with a summary field, it works - but when I try to figure the average amt. of grant funding received per student, I can't create a summary field, because I can't use a summary field (total) in creating a new summary field (average).

       

      When I changed the original summary field (to add up each student's grants) to a calc field, it stopped working, because Sum (amount) only showed the total for each grant, not for all grants received by each student. 

       

      How should I approach this? I'm stumped. I read about using GetSummary, but I couldn't get it to work. No matter how I try to define the average, it comes back as an average of each student's grants, not all students' grants.

       

      Thanks! 

        • 1. Re: Can't Determine Average Based on a Summary Field
          pamaj
             Are you approaching from student side or grant side (which I assume are linked by somthing like the student's ID number)?  From the student side, you should be able to have an "Average Grant" calculation field that looks something like Average(grant::amount) without relying on any other field.  You can then view that field on the grant side so you can see it in both places.
          • 2. Re: Can't Determine Average Based on a Summary Field
            mrvodka
              

            I think your post has some conflicting info...

             

            Are you trying to find the average of grants per each student.

            Student 1 Avg: 50 ( 100, 50, 0 )

            Student 2 Avg: 60 ( 40, 60, 80 )

            Student 2 Avg: 60 ( 30, 50, 100 )

             

            or are you trying to find an average 

            810 ( 100 + 50 +0 +40 +60 +80 +30 +50 +100 )

            ----

            9   ( 9 records )

            • 3. Re: Can't Determine Average Based on a Summary Field
              pamaj
                

              Oops, hit post too quickly... if you're looking for an average of ALL grant amounts, independent of student recipient, that should be done on the grant side, where you can have a summary field doing an average of amount. 

              • 4. Re: Can't Determine Average Based on a Summary Field
                comment_1
                  

                Define the following fields:

                sTotalAmount, Summary = Total of Amount
                cFraction, Calculation = Amount / GetSummary ( sTotalAmount ; StudentID )
                sUniqueCount, Summary = Total of cFraction
                cAvgPerStudent, Calculation = sTotalAmount / sUniqueCount

                 

                Keep in mind that records must be sorted by StudentID for this to work.



                • 5. Re: Can't Determine Average Based on a Summary Field
                  Pine
                     Thanks, I will try this.
                  • 6. Re: Can't Determine Average Based on a Summary Field
                    Pine
                      

                    Hi,

                     

                    I can do your first example (average of the various grants, by student, with a different average for each student), but it's not what I want.

                     

                    I don't understand the numbers in your second example, because I'd get 56.6 [ 510 / 9 = 56.6 ]. Even though I don't understand the numbers, I don't see that what I want, because it looks like all the individual grants would simply be added up and divided by the number of grants, to get the average transaction. (Each grant is a transaction, and a student might get one or many).

                     

                    I want the average funding received by each student. This would need to be based on the total each student received, not on the individual grants (transactions) that make up the total. 

                     

                    Here is what I'm trying to do:

                     

                    Stu 1: (100 + 300 + 200 = 600

                    Stu 2: (150 + 200) = 350

                    Stu 3: (400) = 400

                     

                    Average received per student = 450 = (600 + 350 + 400) [i.e., total by student]  /  3 [i.e., number of students]

                     

                    Because my total by student is a summary field (because it won't work as a calc field because there are multiple records based on the number of grants received by each student), I can't find a way to make FMP spit out the average received per student.

                     

                    Thanks!

                    • 7. Re: Can't Determine Average Based on a Summary Field
                      pamaj
                         AH... now I have a better understanding... it sounds like what you need to do is setup a grants-to-grants selfjoin by student recipient (ID), and create a calculation field to mark the first student instance as unique (or value 1) and subsequent student instances as duplicates (or value 0).  You could then do a calculation for sum(amount)/sum(instance).  I'm home now, but I have a database at work where I have a similar setup (determining unique/duplicate) so if you need help with that I can send info along tomorrow.
                      • 8. Re: Can't Determine Average Based on a Summary Field
                        comment_1
                          

                        pamaj wrote:
                        setup a grants-to-grants selfjoin by student recipient (ID)

                        It's generally not a good idea to use relationships when summarizing the found set.


                        • 9. Re: Can't Determine Average Based on a Summary Field
                          pamaj
                             The other thing you could do, because, as comment noted, summarizing found sets across relationships is problematic, is set up a secondary X relationship between grants and students (something that'll never match so that you get ALL students as a result, like the pk# with the student ID), and then setup your AveragePerStudent calculation in grants as sum(amount)/count(students::ID).  The two methods I've mentioned will only be accurate if you're dealing with ALL records, not a subset.  I'm not sure about how to deal with the subset issue.
                          • 10. Re: Can't Determine Average Based on a Summary Field
                            comment_1
                              

                            pamaj wrote:
                            I'm not sure about how to deal with the subset issue.

                            I thought I had provided an answer.


                            • 11. Re: Can't Determine Average Based on a Summary Field
                              Pine
                                

                              Hi,

                               

                              I appreciate everyone's comments, and look forward to having time to try them tomorrow evening.

                               

                              About the subset issue... we will need something that works for a found set (subset) of students, because we might need to find, say, the average amount of funding received per student over some time frame (last five years, etc.).

                               

                               

                              • 12. Re: Can't Determine Average Based on a Summary Field
                                comment_1
                                  

                                Pine wrote:
                                something that works for a found set (subset) of students,

                                Ahm... I think you mean a subset of the records in Funding - where hopefully the dates and the amounts are.

                                 

                                 

                                BTW, you could do this from the Students table, too - by defining another relationship to the Funding table, filtered by date range. Then you can average the calculation of Sum ( FundingInRange::Amount ) - provided that only students that did receive funding within the given period are included in the found set. But this method is less flexible, because it doesn't allow for a real find: you cannot easily change it to show only grants of certain kind, for example.


                                • 13. Re: Can't Determine Average Based on a Summary Field
                                  Pine
                                    

                                  Hi,

                                   

                                  I tried this, but I'm getting a question mark. You wrote:

                                   

                                  Define the following fields:

                                   

                                  sTotalAmount, Summary = Total of Amount
                                  cFraction, Calculation = Amount / GetSummary ( sTotalAmount ; StudentID )
                                  sUniqueCount, Summary = Total of cFraction
                                  sAvgPerStudent, Calculation = sTotalAmount / sUniqueCount

                                  Keep in mind that records must be sorted by StudentID for this to work.

                                   

                                  ***

                                   

                                  I have an amount per grant transaction, a total amount received per student, and a total amount received by all students. When you define Amount and Total Amount, I think I know which ones you're referring to, but I tried all the different variations, and still couldn't get it to work.

                                   

                                  I think you are suggesting dividing the individual grant amounts by the total received by each student, then adding up the fractional parts of each student's grants (.33 + .50 + .17) to get the unique count of individuals. Then, to get the average grant total per student, we'd divide the total amount by that unique count.

                                   

                                  It lets me define these fields without complaint, but only generates a question mark. Do you have any further thoughts or suggestions? 

                                   

                                  Thanks!



                                  • 14. Re: Can't Determine Average Based on a Summary Field
                                    comment_1
                                      

                                    Pine wrote:
                                    I have an amount per grant transaction, a total amount received per student, and a total amount received by all students.

                                    I am not sure what you mean by that. To define the four fields I have suggested, only two fields are required in the Fundings table: StudentID and Amount.

                                     

                                    The Amount field is the actual amount received by the student in a single transaction. It should be data, not a calculation or a summary.

                                    StudentID is the foreign key to the Students table. Note that you cannot use a related field as the breakfield parameter in GetSummary().

                                     

                                     

                                    Your analysis of the method is correct.

                                     

                                     

                                    What happens when you click into field with the final result?  Perhaps the field just isn't wide enough to display the result - you should format it to display a fixed number of decimal digits.


                                    1 2 3 Previous Next