[Nina Williams]

Hi All,

My boss is looking for a report that would show those donors who have given 17- 20 of the last 20 years. The consecutive giving report does not seem to be adjustable to include a few skiped years. Any ideas?

[stancliff]

I have been faced with a similar challenge. But, for our database, which has over 250,000 constituents, and over 750,000 gifts, the canned report bombs each time we try and run it. I am considering a purchase of SPSS software to accomplish this, or doing something in MS Access. I haven't put too much thought to it, but at the highest level, I was going to create a binary field for each fiscal year of giving, and the constituent would get a 1 if they gave in that fiscal year, and then a 0 if they did not. Then I can add up the score for the past 25 years. In my case, I want at least 5 years of consecutive giving - so I can remove anyone with a score of less than five. From there, I haven't put too much thought into figuring out if the giving is in consecutive years.

Have others tackled this problem?

[hainslie]

I had a similar challenge and this is what I did (it is a little tedious):

Create a query of those gave first gift in 1988 & last gift in 2008. Then create queries for first gift 1989, last gift 2008, First gift 1999 & last gift 2008, etc. Do all the dates that would round up those that have given within 17 years. Merge queries with the 'or' operator.

In the output field for the final merged query, use summary information-summary for gift-total amount of gifts. For field criteria, put in gift date between 1/1/1988 & 12/31/1988. You also might want to include 'gift type is one of' to capture pledge payments, etc. Then do another summary information output for 1/1/1989 to 12/31/1989. Again for all the rest of the years.

You can then see who gave consistently. If you need to capture these people for solicitations, recognition, etc., I would code their records in some way.

Not the cleanest way to do it but it gets you the information you need.... eventually!

[Harriet]

There is a very simple solution - use a Pivot Report. My columns are year and constituent count (counts number of gifts for each year.) It allows you to show donations by year, with blanks in years not given. I just ran a similar report to find a list of donors who have given in at least different 10 years (consecutive or not) out of the last 15 years.

I wanted to add an attribute to those who met certain criteria, so I exported the data to Excel and added a Count function to the end of each line, and that gave the total number of different years each person gave. I then created an import file to add the attribute for those with 10 or more years given.

[Melissa Siobhan]

Unfortunately pivot reports run only on calendar year. You can not in any way I know get it to give you this type of report summarizing on fiscal year.

[stancliff]

Quote:

Originally Posted by Melissa Siobhan

Unfortunately pivot reports run only on calendar year. You can not in any way I know get it to give you this type of report summarizing on fiscal year.

Yes, this is a pain. I discussed this with our BB account manager and a senior consultant a few weeks ago.

For those who don't know, here is a simple formula to use in Excel to convert a gift date into fiscal year.

=if(month(a2)>=#,year(a2)+1,year(a2)),
where a2 is the cell reference of the gift date,
# is the starting month of your fiscal year
The way this formual reads is: If the month of your gift date is greater than or equal to the starting month of your fiscal year, add 1 to the year of the gift date, otherwise use the gift date.

Here is a use case:
Gift date 11/1/2007
Your fiscal year is July 1 - June 30
The month of the gift date is November, thus the fiscal year is 2008. The formula will add 1 + 2007 and viola, you have the correct FY of 2008.