Go Premium for a chance to win a PS4. Enter to Win


Consecutive Years

Posted on 2014-04-15
Medium Priority
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 101

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
New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

LVL 101

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 101

Accepted Solution

mlmcc earned 1600 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: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say 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

I hate sub reports and always consider them the last resort in any reporting solution.  The negative effect on performance and maintainability is just not worth the easy ride they give the report writer.  Nine times out of ten reporting requirements…
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 …
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …
Suggested Courses

963 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