Number of Years Given

Jennifer Patterson

What's the best way to find out the number of years someone has given during a specific date range?

I need to pull the following lists:

1. People who gave at least 1 year from 7/1/09 - 12/31/14
2. People who gave at least in 2 different years from 7/1/09 - 12/31/14
3. People who gave at least in 2 different years from 7/1/09 - 12/31/14 and have given in 2015

Consecutive years report won't work becuase it doesn't have to be in consecutive years.



Stumped on where to even begin with this one.

Carolyn Moatz

I'm not sure if this is the best way, but I'd run a list of everyone who gave between 7/1/09 and 12/31/14, then export it with gift summary, number of gifts for each year you are looking for (e.g. 7/1/09-12/31/09, 1/1/10-12/31/10, etc.), then use a formula in excel to return the number 1 for each year (=if(b2=0,"",1)), then add those columns for the total.

JoAnn Strommen

Basically you'll need to create queries to group the records of constituents with gifts during that period and take the query to Export.  Export the data to excel and sort/filter/forumula there to find those who meet your need list.



As Carolyn posted ... except I would include column for 2015 so you can get third list at the same time.



 

Jen Claudy

I would write a Constituent Query using the Summary fields.  Once you have your constituents selected in the Query, you can set up the same Summary fields in a Constituent Export and add any other necessary data.  (Or, if you only need very basic fields that aren't causing duplicate records, you can export from the Query.  This is generally not recommended, and I wouldn't attempt it unless you are confident in working with Queries.)



Start a new Constituent Query.  Under Available Fields, open the Summary Information section near the bottom, then open the Summary For Gift section.  Select Total Number of Gifts and enter the Criteria as "greater than or equal to 1".  On the Filter Tab, select the Gift Type field and set to "one of" with all applicable gift types that you want.  This is important, as the Summary Fields "think for you" and assume you don't want to include both pledges and payments...and I don't remember which it defaults to because I always set them myself just to be sure it gives me what I want.  Then use either Gift Date or GL Post Date (whichever is most appropriate for your organization) and set to "between 7/1/2009 and 6/30/2014".



Move to the Output Tab, and repeat the process 6 more times, once for each fiscal year from FY10 to FY15.  Unfortunately, there is no copy feature for summary field criteria. =(  You can expand the first one so you can see what you selected and make all the others identical.  And you can type "G" to jump to the Gift section of the Filter's Available Fields ("I" for easy selection of GL Post Date or Gift Type).



Once the fields are in the Output window, you can right click and change the Column Heading to "FY10" etc.  Add Constituent Name and Constituent ID to the Output as well, so you can easily spot check your results.  For viewing purposes in Query Results, you can use Ctrl+A to resize the columns...if you do this, definitely shorten the column names or it won't help much.



This should be the first list you specified.  Spot check the results from the Query, then I would Save As for a backup.  You can just export this data and manipulate it outside RE (in Excel or Access, for example), or you can continue in RE and alter the Query Criteria.



To continue in RE, remove the 5-year Summary Field and set up a long series of Criteria in pairs: 1+ gifts in FY10 AND 1+ gifts in FY11 OR 1+ gifts in FY10 AND 1+ gifts in FY12 OR 1+ gifts in FY10 AND 1+ gifts in FY13...etc.  Because you already set up each FY as a Summary Field on the Output Tab, they will all be available in the Query Fields section of the Available Fields window (first section at the top)...unfortunately, custom column names don't appear here, so if you didn't do them in order, jot down which is which.



For the third of your lists, you will want to add the FY15 Summary Field as the first or last of your criteria and put parentheses around all of the criteria from the second list.  So 1+ gifts in FY15 AND (1+ gifts in FY10 AND 1+ gifts in FY11 OR 1+ gifts in FY10 AND 1+ gifts in FY12 OR 1+ gifts in FY10 AND 1+ gifts in FY13...etc.).



This sort of requires you to have a basic knowledge of writing queries, so if this makes no sense, let me know and I can try to either explain better or walk you thru it over the phone.  And possibly someone will post an easier solution.

  

JoAnn Strommen

Jennifer's query looks like it would work but it sure is a lot of work - you're talking 15 pairs of criteria to find those who gave 2 years!!

Query is a grouping tool.  That's why others would suggest querying all who gave a gift within your dates and then taking that 'group' to Export and there outputting the summary info for each year. 



Don't forget to specify gift types if you record pledges and/or recurring gifts.  If you don't your data may be skewed: pledge in 12/2010, payment in 1/2011 would pull as 2 gifts/2 years.

Christina Swiszcz

I also use the type of query Jennifer mentioned. It takes some time to set it up initially, but once it is set up correctly you can keep reusing the same query each year, just changing the dates. It sounds like you would need 3 separate queries for each of the groupings that you want. Then you could export only those constituents that meet the criteria without having to sort through the spreadsheet.