5 Replies Latest reply on Sep 25, 2011 1:18 AM by Sorbsbuster

    Help with Calculation

    brianquillin

      Title

      Help with Calculation

      Post

      I am tracking Contributions.  Each Contribution record contains a Season field.

      What calculation would I use to generate a Contact's TOTAL Contribution Amount per Season?

      [Seasons are entered as four digit years, ie. 2010, 2011, 2012, etc]

      [I already have the total_amount calc field defined, but it totals ALL Seasons, not BY Season]

      If (Season = ????? ; total_amount)

       

      Thanks in advance...

        • 1. Re: Help with Calculation
          Sorbsbuster

          One way is to create a self-relationship from the Season field to the Season Field.  Then have a calculation:

          SeasonTotal = Sum ( SelfBySeason::ContributionAmount )

          Each record will show the total for the same season as it is.

           

          If you want it for that season for that person, then make the relationship by Season and By PersonID.

          • 2. Re: Help with Calculation
            brianquillin

            I didn't clarify my initial statement very well.  Season is a text field in Contributions.  

            Contributions::season 

            I have tried to find more infomation on using "self" statements, but I've not been successful.  Can you give me more info on a self-relationship? 

            Let me know if I should provide more information.

            Thanks...

            • 3. Re: Help with Calculation
              Sorbsbuster

              It is ok that Season's is a text field.

              Create another Table Occurance of the table Contributions.
              Rename it as contributions_SelfByContactSeason

              Create another relationship between the Contribution Table and that new Table Occurance
              Join the two by:

              Contribution::ContactID = contributions_SelfByContactSeason::ContactID
              AND
              Contribution::Season = contributions_SelfByContactSeason::Season

              Then create the calculation:
              SeasonTotal = Sum ( contributions_SelfByContactSeason::ContributionAmount )

              Each record will then show the total for the same season and contact as it is for.

              • 4. Re: Help with Calculation
                brianquillin

                That got it!

                And as usual, one solution creates another needed issue.  I have a report to show:

                Contact Name | Season | Total Amount | 

                If I use the Contacts Table, it only shows ONE season for each contact.

                If I use the Contributions Table, it shows ALL Contributions (meaning that each lesser contribution registers in the report but the Total Amount is now listing TWICE).

                Any thoughts on how I should proceed to remedy this report?

                Really appreciate your help. I saw your response to my other issue.  I'll be working on that shortly.

                • 5. Re: Help with Calculation
                  Sorbsbuster

                  Find all records for 2012, say.  Have a layout in the Contributions table, sort by Contact, have a Subsummary by Contact, remove the body part, and show that field in the sub-summary.