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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

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

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

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…
Hello everyone, Hope you find this as helpful as we did. We have on the company I work for an application built in Delphi V with Crystal Reports 8. We all know that Crystal & Delphi can be temperamental sometimes and the worst thing is, nearly…
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

708 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now