AnsweredAssumed Answered

Stop calculating at the end of a year

Question asked by CaitlinMcKelvie on Jan 3, 2014
Latest reply on Jan 3, 2014 by philmodjunk


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!