2 Replies Latest reply on Dec 29, 2010 10:49 AM by philmodjunk

    How to calculate a total based on year to date?



      How to calculate a total based on year to date?


      Filemaker Pro Pals:

      Hmm. Just can't figure out how to do this. I have two tables linked by ID. So, in table 1 I have a portal to table 2. Now, what I am doing is tracking clients for a non-profit who donate money. So Client A donates money on a particular date, and then donates again on another date. I want to have a calculated field that shows the total money donated. That part is easy by using the sum function. But, I want to track it by current calendar year. So, say the client donates money last year, and now this year. I want the calculated field to show up for this year only. How do I structure this? There are two fields involved in the calculation: One is the date of transaction, and the other is the amount. I tried using the case function, but I just can't figure out how to do it. Any thoughts here?

        • 1. Re: How to calculate a total based on year to date?


          You can make use of "year" function (Year("12/29/2010") will return 2010), on this basis u can select for the same year and get the sum.

          • 2. Re: How to calculate a total based on year to date?

            If you want to pull up a client record and see the year to date total donations, do this:

            Define an unstored calculation field in Client, cThisYear, as Year ( Get ( CurrentDate ) )

            As Raj kumar suggests, define calculation field, cYear, in Donations as Year ( TransactionDateField ) // use your field name here

            Define a new relationship:

            Client::ClientID = DonationsThisYear::ClientID AND
            Client::cThisYear = DontaionsThisYear::cYear

            DonationsThisYear is a new table occurrence of your donations table. Select your original donations table occurrence in Manage | Database | Relationships and click the button with two plus signs to create it. Then link it as shown above to your existing Client table occurrence.

            Now Sum ( DonationsThisYear::DonationAmt ) will compute the year to date total.