I have been asked to query a group of constituents who have given more than $750 in a 12 month period during the last three years, but not necessarily during a calendar or fiscal year. In other words, if a constituent gave $500 in December and $250 in March, since this would not fall into a calendar year I could use fiscal year, but by using fiscal year, I would miss anyone who would have given the same amount in a calendar year. In the same respect, if someone gave $500 in July and $250 in October, since our fiscal year begins in August, I could use calendar year, but would miss anyone who would have given the same amount in a fiscal year. Again, this data is requested for anyone giving this amount during the last three years.

Three questions: 1) Is this possible, and if so, 2) how, or, 3) is there an alternative method of segmentation used as a standard practice?

Any help will be greatly appreciated.

I don't know of a way to do it with Raiser's Edge's existing capabilities. If you export the data and use MS Access or Excel to analyze the data, (or crystal reports, maybe) it is possible. If you are not experienced with complex formulas in these data analysis programs, I expect it'll be difficult for you to segment out the group of constituents you are looking for. I can't explain in a brief post how you would go about it. If you just want to do one query by calendar year and another by fiscal year, that would be easier but would not achieve exactly the same result. To do a closer approximation of what you've been asked for you could do as follows (several 12 month periods three months apart) for example, total-amount-of-gifts > 750 -where- gift date between 7/1/07 and 6/30/08 -or- , total-amount-of-gifts > 750 -where- gift date between 10/1/07 and 9/30/08 -or- total-amount-of-gifts > 750 -where- gift date between 1/1/08 and 12/31/08 -etc etc-

I'm in the middle of globally adding an appeal so I can't get to query to be more accurate, but I have done queries like this often and, unless I'm misunderstanding your goal, this should be achievable in a query. I believe you can do this using specific date range. 

 

 

Do a Constituent query,

Summary Information/Summary for Gift/Total Amount of Gifts/Operator Greater than $750.00. 

Select the Filter Tab/Gift Date operator Between then enter your dates.

How does that get an output of a 12 month period within the last 3 years?

Tracie Cassidy:

How does that get an output of a 12 month period within the last 3 years?

Ah, sorry, I missed that part. 

Darn, I was hoping you'd figured out some brilliant way to do it!

 

I mean, I guess you could do it as a series of a bunch of OR summary fields:

 total gift amount >= $750 between 1/1/07 and 1/31/08

OR total gift amount >= $750 between 2/1/07 and 2/28/08

OR total gift amount >= $750 between 3/1/07 and 3/31/08

etc etc etc.

If you did not have to do a lot of other filtering, this might just be crazy enough to work. If you filtered on much more than gift date, though, I think this would be too much work to be worth it.

 

Don,

Is the 12 month period based on just month and year, or month and day and year?

For example: if a $500 gift comes in on 10/15/2009 and another $500 gift comes in on 10/30/2010, would those be considered as $500 in Oct 2009 and $500 in Oct 2010? If so, then that's $1000 within your 12 month reporting window, but if you're factoring in the exact gift date, then those gifts wouldn't pull into your report since they're more than 12 months apart.

There might be a way to pull this off with RODBA, but the answer to the question above will play a big part in that.

 

Second question: when you say "12 month period", you mean any 12 consecutive months, right?