3 Replies Latest reply on Mar 9, 2015 8:10 PM by philmodjunk

# Rentention Rate Calculation

### Title

Rentention Rate Calculation

### Post

Hi everyone, I did not see any existing posts about this, but if there is, please direct me to it.  I need to calculate our donor retention rate comparing one year to the following year.  In this case, 2013 vs. 2014.  In the attached picture is a dummy record showing the donor made a membership donation in 2013, and then again 2014, so I would want a calculation that compared those two items and returned a 1, which I could then sum to get a count of all of the donors who gave both years.  The portal is showing related income records from a different table.  If they gave one year but not the next, it would return a 0 and thus they would not count as "retained."

For now I just want the retention % number, but later I hope to create a chart that shows this for different date ranges, or shows a floating retention rate from the same month of the previous year.  In other words, I have larger aspirations which dictate any solution you might offer, so just throwing it out there.

Any help would be much appreciated.

• ###### 1. Re: Rentention Rate Calculation

This can be done with execute SQL or with a pair of relationships using duplicated table occurrences. The first option is only possible if you know SQL and are using FileMaker 12 or newer, but is the simpler way to go for this calculation.

The "multiple occurrence" method:

You already have this relationship presumably:

Donors::__pkDonorID = Donations::_fkDonorID.

Your names may be different but that would produce the results shown in your portal.

If you make some duplicates of the Donations occurrence (click button with two green plus signs), you can do this:

Donors::__pkDonorID = Donations|Year1::_fkDonorID AND
Donors::gYear1 = Donations|Year1::cYear

gYear1 would be a number field with global storage specified so that you can just enter the desired year once and have it work for all donors. cYear would be a calculation field with a number result type and this expression: Year ( DonationDate ).

You would repeat this for a third occurrence of donations, and a second global field:

Donors::__pkDonorID = Donations|Year2::_fkDonorID AND
Donors::gYear2 = Donations|Year1::cYear

Then this expression, defined in a calculation field in the donors table would return a 1 for every donor that made at least one donation in both specified years:

Not IsEmpty ( Donations|Year1::_fkDonorID ) and Not IsEmpty ( Donations::year2::_fkDonorID )

You'll be able to use this year after year by selecting different values in the two global year fields.

• ###### 2. Re: Rentention Rate Calculation

Thanks PMJ, this might be over my head but I will try it soon.  Right now I just wanted to say thanks!

• ###### 3. Re: Rentention Rate Calculation

If "table occurrence" is unfamiliar, see:Tutorial: What are Table Occurrences?