Consecutive Years

Posted on 2014-04-15
Last Modified: 2014-04-24
Hi there,

I have a report to create that identifies the consecutive years that a donor has contributed.

All of the gift information is in one table and so far I have come up with:
Essentially if the gift date is the same year as today and another gift is in the year prior to this, then it should return the value of two.  (I have to code up to 25 years for this report!)

if year({CnGf_1.CnGf_1_Date}) = year (today)
and year ({CnGf_1.CnGf_1_Date}) = year (today)-1
then 2

else if year({CnGf_1.CnGf_1_Date}) = year (today)
then 1

Do you have any ideas how to proceed?

Thank you in advance,
Question by:rporter45
  • 3
  • 3
LVL 100

Expert Comment

ID: 40002785
You only have 1 record to work with.  I assume there is a record for each contribution with the appropriate date.

Can there be multiple contributions in a year?

Try this idea
Create aformula
Name - YearofContribution
Year ({CnGf_1.CnGf_1_Date})

Put it in the detail section of the report.
Right click it
Set the summary type to COUNT DISTINCT
Set it to insert into the DONOR group footer
Click OK

You can then take the formula off the report

Run it

LVL 34

Expert Comment

ID: 40003565
If, as you said, you're actually looking for _consecutive_ years, that won't work.  It will just give you a count of the years.

 Assuming that you're looking for consecutive years ...

 You mentioned 25 years.  In that 25 year span, are you only looking at people that donated this year, or anyone that donated at any point in the last 25 years?  For example, could you be looking at someone that donated every year, from 2001 - 2005, but not since then?

 If you are including all donations in the last 25 years, what if someone donated in more than one group of years, like 2001 - 2005, and then from 2007 - 2010, but not since then?


Author Comment

ID: 40003893
Hi there,

The key requirement is that the donor must have given in the current year.

From there, to qualify as 25 consecutive, there must be a gift in each year from 2014 to 1989

To qualify as 24 consecutive years, there must be a gift in 2014 to 1988, all the way to 2 consecutive years, with a gift in 2014 and 2012.

All other possible combinations between do not matter for this as they will not be consecutive.

I was thinking of doing a formula for each year, if there is a gift then 1 else 0.  From there, a formula that would essentially say if formula for 1989, 1988, 1987, etc. then 25 consecutive years.  I started this but am not sure how to put it into a formula to subtract 1 year from the current date.

I hope this helps!

thanks - CS
Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

LVL 100

Expert Comment

ID: 40003996
Are you only looking to get this information or does the report have other information to show?
I ask because to do this you probably need to sort the data by donation date descending which might cause issues with other information

See the attached report.


Author Comment

ID: 40004092
Hi there,

I have to group by the ID number only so this may work! I will review and get asap,

Thanks very much!
LVL 100

Accepted Solution

mlmcc earned 400 total points
ID: 40004199
To use my solution you need a group on the person, ID or Name and a group by the donation year sorted descending.

Without those groups my solution won't work.


Author Closing Comment

ID: 40020734
Hi there,

I had to make some changes to the report situation but that was to be expected! All is working - thanks very much!


Featured Post

ScreenConnect 6.0 Free Trial

Explore all the enhancements in one game-changing release, ScreenConnect 6.0, based on partner feedback. New features include a redesigned UI, app configurations and chat acknowledgement to improve customer engagement!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Insert timestamp data field of max summary 8 43
Formatting a Crystal Report 2 58
Crystal Reports--SubReport 3 56
Exclude some records from totals 10 57
Crystal Reports: 5 Tests for Top Performance It is complete, your masterpiece report.  Not only does it meet your customer’s expectations, it blows them out the water, all they want is beautifully summarised and displayed in a myriad of ways. …
Hot fix for .Net Crystal Reports 10.2.3600.0 to fix problems with sub reports running on 64 bit operating systems ISSUE: Reports which contain subreports fail with error "Missing Parameter Value" DEPLOYMENT SERVER OS: Windows 2008 with 64 bi…
This Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.

778 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question