Stop calculating at the end of a year
I have kind of a complicated question, and I am not sure if it is possible to do this in FileMaker. I am trying to calculate the percentage of active members that make a donation to our organization during a year. This is the important information from my database:
- Member Unique ID- Links it to the Member
- Date of donation
- Year of donation (calculated by the function year (donationdate))
- Member Unique ID- Links to the donation
- Active (Yes or No)
- Date Created
I also have a global date field that I use with a drop down to select a year. (glob::year)
We are constantly adding members and changing members from active to inactive. What I am trying to do is calculate the percentage of members that were active in 2013 that made a donation. However, I don't want this number to change in 2014 if we add more active members, or make members that were active in 2013 when they made the donation but are now inactive.
I don't have a problem with creating the calculation to give the percentage of active members that have donated during the year based on the information that is in the database right now.
Field A= Execute SQL ("Select Count (Distinct Member Unique ID) from Donations where year = ? and Members::Status = 'Active'"; "";""; glob::year)
Field B= Execute SQL("Select Count (*) from Members where Status = 'Active'"; "";"")
Field C= Field A/ Field B
The problem is that the numbers from 2013 change in 2014. Is there a way to get the number for 2013 to stop changing when 2014 starts? I am fine with creating a calculation field for each year if necessary, but I am not sure how to go about doing this. Thanks for your help!