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 35

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
Secure Your Active Directory - April 20, 2017

Active Directory plays a critical role in your company’s IT infrastructure and keeping it secure in today’s hacker-infested world is a must.
Microsoft published 300+ pages of guidance, but who has the time, money, and resources to implement? Register now to find an easier way.

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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

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. …
There have always been a lot of questions related to when Crystal Reports evaluates report components (such as formulas, summaries, cross-tabs, charts, to name a few examples). Crystal Reports uses a two-pass reporting process to provide greater …

730 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