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

# How to calculate a total based on year to date?

### Title

How to calculate a total based on year to date?

### Post

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?

Hi,

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.