Solved

Consecutive Years

Posted on 2014-04-15
7
283 Views
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,
Colleen
0
Comment
Question by:rporter45
  • 3
  • 3
7 Comments
 
LVL 100

Expert Comment

by:mlmcc
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
Click INSERT --> SUMMARY
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

mlmcc
0
 
LVL 34

Expert Comment

by:James0628
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?

 James
0
 

Author Comment

by:rporter45
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
0
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.

 
LVL 100

Expert Comment

by:mlmcc
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.

mlmcc
Donation.rpt
DOnations.xls
0
 

Author Comment

by:rporter45
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!
CS
0
 
LVL 100

Accepted Solution

by:
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.

mlmcc
0
 

Author Closing Comment

by:rporter45
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!

CS
0

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

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 …
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

808 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