Count Consecutive Years 1

[IbeKaba]

I'm using Crystal 9, pulling from a SQL database, using ODBC connection.

I'm pulling records from two tables. One table has general info, and the other has gift info. The two tables are linked by ID. Since there are many corresponding gift records for a donors, I have my report grouped by ID. Hence for each ID I have listed all the years that donor has made a gift in.
Example:

ID GftYear
111
2006
2005
2004
2001
222
2006
2004
2003
2001
333
2005
2004
2002

Here is the deal: I would like to know, for example, how many people have given three consecutive years in a row. (In the above example that would be only ID 111). Is this possible in Crystal? If so, how can I go about achieving it?

Thanks a lot in advance
ibe

 

[lbass]

Try creating a formula {@threeconsec}) to be placed in the detail section:

whileprintingrecords;
numbervar consec;

if {table.ID} = previous({table.ID}) and
{table.ID} = next({table.ID}) and
previous({table.year})-next({table.year}) = 2 then
consec := consec + 1;

In the group header for {table.ID} (insert a group if you don't have one), add this formula:

//{@reset}:
whileprintingrecords;
numbervar consec := 0;

In the group footer for ID, add this formula:
whileprintingrecords;
numbervar consec;
numbervar consectot;

if consec >= 1 then
consectot := consectot + 1;

Then in the report footer, use:

whileprintingrecords;
numbervar consectot;

-LB

 

[synapsevampire]

LB: If they have 4 consecutive years, I think your formula will count it as 2 occurences, since they want the number of people, not counts of 3 year periods, I think you need to add some checks, something like:

Group Header formula
whileprintingrecords;
Booleanvar Counted:=False;
numbervar Got3:=0;

Details formula
whileprintingrecords;
numbervar Got3;
numbervar MyCount;
if not(onfirstrecord)
and
Not(Counted) then
(
if previous({table.id}) = {table.id} then
Got3 := Got3+1;
If Got3 = 3 then(
MyCount:=MyCount+1;
Counted:=true
);

Report Footer formula
whileprintingrecords;
numbervar MyCount

-k

 

[lbass]

No, actually it will only count an ID once.

-LB

 

[synapsevampire]

Ahh, got it:

if consec >= 1 then
consectot := consectot + 1;

Thanks.

-k

 

[IbeKaba]

Thanks guys. I think I understand your answer(s)....but I think I explained my problem wrong. The 3 consecutive was just an example. What I really need to know is how many consecutive years a donor has given. Therefore in the above example, I not only need to know ID 111 has given three consecutive years in a row, but also that ID 222 and 333 have given two consecutive years in a row each.

So LB I thought I could modifiy your code (@threeconsec) to have consec return 1 for each time {table.ID} = previous{table.ID} and{table.year} - next{table.year}=1. Then in the group footer I can subtotal @threeconsec, and base on this figure I can determine how many consecutive donation were made by a particular donor. Unfortunately as it is, I can not summarize @threeconsec. And I'm not even sure if the above algorithm is right.

Anyway,

More detail:
my record are from a six year period.

As result, I would like to know X number of donors have given 5 years in a row, Y number of donors have given 4 years in a row, Z number of people have given 3 years in a row, etc.

What about if a donor gives 2006, 2005, 2003, 2002, and 2001? Although they have given 3 consecutive times before, I only need to know their current streak. Therefore, they will fall under 2 consecutive donations in this case. Basically, I would like the code to stop and move to the next group if 1 is not the answer.

I hope that makes it a little clearer. My apologies for not even more details earlier.
Whether you can still help me or not, please know that I appreciate the help you've given me already.
Thanks a lot,
ibe

 

[lbass]

Here is a solution that seems to work with fabricated data. First, insert a group on ID and then sort your records by the gift year in ascending order. In the following formulas I'm using {@year} (number datatype) to represent your gift year. Create the following formulas (Note that new formulas are preceded by //{@Name}-otherwise you are seeing the same (long) formula):

//{@reset} to be placed in the ID group header:
whileprintingrecords;
stringvar x := "";
stringvar y := "";

//{@accum} to be placed in the detail section:
whileprintingrecords;
stringvar x;
numbervar i := 0;
stringvar y;

if instr(x,totext({@year},"0000")) = 0 then
x := x + totext({@year},"0000")+", ";

stringvar array z := split (x,", ");
numbervar j := ubound(z);

for i := 1 to j do(
redim preserve z[j+1];

if
(
(
i = 1 and
val(z) = val(z[i+1])-1
)
or
(
i > 1 and
val(z) <> val(z[i-1])+1 and
val(z) = val(z[i+1])-1
)
) and
instr(y, z) = 0 then
y := y + z + (if len(z) <> 0 then "-") else

if
val(z) < val(z[i+1])-1 and
instr(y, z) = 0 then
y := y + z + ", " else

if z = z[ubound(z)-2] and
{table.ID} <> next({table.ID}) and
instr(y,z) = 0 then
y := y + z else
y := y
);
y;

//{@displaymostrecentstreak} to be placed in the ID group footer:
whileprintingrecords;
stringvar y;
stringvar array k;
if instr(y,", ") > 0 then
k := split(y,", ") else
k := y;

numbervar b := 0;
numbervar c := ubound(k);
redim preserve k[c];
stringvar array m := "";
numbervar g := 0;

for b := 1 to c do(
if instr(k, "-") <> 0 then (
g := g + 1;
redim preserve m[g];
m[g] := k)
);
if g > 0 then
m[g] else "";

//{@accumstreaklength} to be placed in the ID group footer (and suppressed):
evaluateafter({@display});
whileprintingrecords;
stringvar array m;
numbervar g;
numbervar h := 0;
numbervar strk0;
numbervar strk2;
numbervar strk3;
numbervar strk4;
numbervar strk5;
numbervar strk6;

if g > 0 then
h := val(split(m[g],"-")[2])- val(split(m[g],"-")[1])+1;
if h = 0 then
strk0 := strk0 + 1;
if h = 2 then
strk2 := strk2 + 1;
if h = 3 then
strk3 := strk3 + 1;
if h = 4 then
strk4 := strk4 + 1;
if h = 5 then
strk5 := strk5 + 1;
if h = 6 then
strk6 := strk6 + 1;

//{@displaystreaklength} to be placed in the report footer:
whileprintingrecords;
numbervar strk0;
numbervar strk1;
numbervar strk2;
numbervar strk3;
numbervar strk4;
numbervar strk5;
numbervar strk6;

"Frequency of Donors by Years of Consecutive Giving: "+chr(13)+
"None: "+ space(18-len(totext(strk0,0,""))) + totext(strk0,0,"")+chr(13)+
"2 Years: "+space(15-len(totext(strk2,0,"")))+totext(strk2,0,"")+chr(13)+
"3 Years: "+space(15-len(totext(strk3,0,"")))+totext(strk3,0,"")+chr(13)+
"4 Years: "+space(15-len(totext(strk4,0,"")))+totext(strk4,0,"")+chr(13)+
"5 Years: "+space(15-len(totext(strk5,0,"")))+totext(strk5,0,"")+chr(13)+
"6 Years: "+space(15-len(totext(strk6,0,"")))+totext(strk6,0,"")

You must right click on this last formula->format field->common->check "Can grow". You should also go to the font tab->font and select a non-proportional font like Courier New or Lucida Sans Typewriter, so that the spacing is appropriate.

-LB

 

[synapsevampire]

Might simplify a bit:

Group Header:
whileprintingrecords;
Numbervar array Counts[15];
Numbervar consyear:=1;

Details:
whileprintingrecords;
Numbervar array Counts[15];
numbervar ConsYear:=0;
if {table.group} = previous({table.group}) then
(
if {table.year} = previous({table.year})+1 then
consyear:=consyear+1
else
Consyear := 1
);

Group Footer formula
whileprintingrecords;
Numbervar array Counts[15];
Numbervar consyear;
Counts[consyear]:=Counts[consyear]+1

Now you have an array of 15 elements (allowing for 15 years, just in case you use the report for awhile...) which contain the counts for each year, adjust the [15] to house more years if need be.

Now the display would be in the Report footer as:

whileprintingrecords;
Numbervar array Counts[15];
numbervar MinYear:= minimum({table.year});
numbervar MaxYear:= maximum({table.year});
numbervar x;
Stringvar Output;
for x := 0 to (MaxYear-MinYear) do(
Output:=Output+ "For " & totext(x,0,"") & " years consecutive: " totext(Counts[x+1],0,"") & chr(13)
);
left(Output,len(Output)-1)

I didn't test, but it's fairly straightforward, if there's a buglet it should be easy to understanmd and resolve.

Bit simpler, allows for any amount of years, and should prove much faster.

-k

 

[synapsevampire]

Ooops, change the details formula to:

Details:
whileprintingrecords;
Numbervar array Counts[15];
numbervar ConsYear;
if {table.group} = previous({table.group}) then
(
if {table.year} = previous({table.year})+1 then
consyear:=consyear+1
else
Consyear := 1
);

-k

 

[lbass]

SV,

I was interested to see if a simpler version would work, so tested your formulas, using my fabricated data. My formulas (with slight changes to allow for two more years) resulted in the following for a set of 202 IDs:

Frequency of Donors by Years of Consecutive Giving:
None: 126
2 Years: 13
3 Years: 11
4 Years: 3
5 Years: 14
6 Years: 10
7 Years: 20
8 Years: 5

Your formulas resulted in:

For 0 years consecutive: 1
For 1 years consecutive: 0
For 2 years consecutive: 0
For 3 years consecutive: 0
For 4 years consecutive: 0
For 5 years consecutive: 0
For 6 years consecutive: 0
For 7 years consecutive: 0

I did change your first formula to read like the following, since it otherwise failed:

whileprintingrecords;
Numbervar array Counts;
redim counts[15];
Numbervar consyear:=1;

The reason I thought this problem was interesting was because it required not only determining a count of consecutive years by ID, but because it entailed identifying the most recent streak of consecutive years, potentially having to ignore the most recent year, or earlier streaks of consecutive years per ID. So step 1 was to identify all streaks. Step 2 was to identify the most recent streak per ID. Step 3 was to calculate the number of years in the streak and then include the ID in the appropriate running count.

I agree there probably is a way to simplify my coding, but I'm not sure what that would look like. In case you want to take a stab at it using the same data, I created the dataset by using the Xtreme database in XI, using the Orders table. In order to create some variation in arrays of years, I used a record selection of:

not ({Orders.Employee ID} in [3,6])

I also created a formula {@years}:
if {Orders.Employee ID} = 1 then year({Orders.Order Date}) + 1 else
if {Orders.Employee ID} = 4 then year({Orders.Order Date}) + 3 else
if {Orders.Employee ID} = 7 then year({Orders.Order Date}) + 5 else
if {Orders.Employee ID} = 9 then year({Orders.Order Date}) + 6 else
year({Orders.Order Date})

I grouped on {Orders.Customer ID} and sorted on {@years} ascending.

-LB

 

[synapsevampire]

G'mornin', LB, early rised there, very industrious.

I tested it with data from the sample Employee report, it works, but I had to add in a formula for a unique year as the data repeats for each year and use the year group as a details section.

I generally espouse theory rather than coding it out:

Create the formula:

@yr
year({Orders.Order Date})

Delete all exisisting groups except one and change it to ship_via

Create another group for the year formula (allowing for a unique year value), and suppress the details.

Place this formula in the ship_via group header:
Details:
whileprintingrecords;
Numbervar array Counts[15];
numbervar ConsYear;
if {table.group} = previous({table.group}) then
(
if {table.year} = previous({table.year})+1 then
consyear:=consyear+1
else
Consyear := 1
);

Place the following in the year group header:
whileprintingrecords;
Numbervar array Counts[15];
numbervar ConsYear;
if {Orders.Ship Via} = previous({Orders.Ship Via}) then
(
if {@yr} = previous({@yr})+1 then
consyear:=consyear+1
else
Consyear := 1
);
Consyear

Place the following in the ship_via group footer:
whileprintingrecords;
Numbervar array Counts[15];
numbervar ConsYear;
Counts[consyear]:=Counts[consyear]+1;
Counts[Consyear]

Place the following in the report footer:

whileprintingrecords;
Numbervar array Counts[15];
numbervar MinYear:= minimum({@yr});
numbervar MaxYear:= maximum({@yr});
numbervar x;
Stringvar Output;
for x := 0 to (MaxYear-MinYear) do(
Output:=Output+ "For " & totext(X+1,0,"") & " consecutive :" & totext(Counts[x+1],0,"") & chr(13)
);

The key to what you found intriguing about it, that being the consecutive years is resolved by:

Details:
whileprintingrecords;
Numbervar array Counts[15];
numbervar ConsYear;
if {table.group} = previous({table.group}) then
(
if {table.year} = previous({table.year})+1 then
consyear:=consyear+1
else
Consyear := 1
);

The table.group check is key, then have the variable already set to 1, allowing for only one row per group, and then resetting it to 1 if it isn't consecutive.

-k

 

[synapsevampire]

I should have said the sample Employee Sales report.

-k

 

[synapsevampire]

For Ibekaba's sake, make sure that you sort the years ascennding.

-k

 

[lbass]

Sorry, SV, but when I test this, setting it up as you suggested, I get error messages, first on the shipper header formula, highlighting:

Numbervar array Counts[15];

If I change it and add a redim statement:

Numbervar array Counts;
redim Counts[15];

...I then get an error message in the shipper footer formula (see bolded area) re: a subscript must be between 1 and the size of an array:

whileprintingrecords;
Numbervar array counts[15];
numbervar consyear;
counts[consyear]:=counts[consyear]+1;
counts[consyear]

-LB

 

[synapsevampire]

Don't be sorry, it works here, but check the theory, verbatim coding isn't very interesting anyway, looking at things from a different perspective is.

If you want a copy of the report, send along an email:

crystalreports at hotmail.

CR XI

-k

 

[lbass]

SV's reset formula actually was:

whileprintingrecords;
Numbervar array Counts;
redim preserve counts[15];
Numbervar consyear:=1;

His consecutive years display showed 1 consecutive year for two-year spreads, 2 years for three-year spreads, and so on, so in order to compare our results, I changed his display formula to:

whileprintingrecords;
Numbervar array Counts[15];
numbervar MinYear:= minimum({@year});
numbervar MaxYear:= maximum({@year});
numbervar p;
Stringvar Output;
for p := 0 to (MaxYear-MinYear) do(
if p+1 = 1 then
output := "No Consecutive Years: "+totext(counts[p+1],0,"")+chr(13) else
Output:=Output+ "For " & totext(p+1,0,"") & " years consecutive: " + totext(Counts[p+1],0,"") & chr(13)
);
left(Output,len(Output)-1)

When I inserted a group on year, and moved my {@accum} to the group FOOTER for the year group, then our results matched (in some cases) with the exception that SV's formulas will count the most recent value or range, while mine will count the most recent range, excluding any more recent, but non-consecutive years (which was what I understood the requirement was). So, for example, in the case of an ID with years:

2002,2003-2005,2007

...my formulas would count the ID for a three-year spread (2003-2005), while SV's will count it as 1 year (2007).

SV's use of number arrays is more elegant than my approach--I'm not that experienced with arrays. I just keep trying until I get them to work somehow. So thanks for the learning opportunity, SV.

-LB

 

[IbeKaba]

You guys are simply AMAZING! I've not had the chance to try your suggestions yet, but I just wanted to say what a nice suprise this is; after a long frustrating friday last week, to come in to work today to this....delicious!
Thanks a lot.
ibe

 

[synapsevampire]

I may have coded to another spec here

If you're looking for the counts for the most recent range, it should be OK, if you want something else, let us know.

It's always interesting working something out with LB, a fine mind and curiousity there.

-k

 

[IbeKaba]

All is well. I got exactly what I was looking for: going by current streak, how many donors have given y years in a row.
However, at first I thought LB's understanding of the problems was right, but after checking my own requirements I think SV's right. That is, in a case of an id with years 2000, 2002, 2003, 2004, 2006, like LB, I at first thought 3 years was right, but after looking it over, actually this donor doesn't qualify for any because he/she has no current streak since their last donation was in 2006 and they had no donation in 2005.

I'll be tweaking your codes a bit to get exactly what I need. I'll let you know if and when I come accross something fishy.

On a personally note, I didn't know knowledge of VB was so important to get the best out of Crystal. I thought it was a point and click program. But I guess like most software packages, that only gets you so far. Eventually you are going to need some coding know-how to get the best out of them

Anyway, you guys are great. This forum is the best thing to happen for my career in a long time!
Merci Beaucoup,
ibe

 

[lbass]

Here is where I got the (mistaken) idea that you wanted to ignore a more recent year, if there was an earlier streak:

the above example, I not only need to know ID 111 has given three consecutive years in a row, but also that ID 222 and 333 have given two consecutive years in a row each.

So in the case of 222, you were ignoring the year 2006 donation. Apparently that wasn't what you ended up wanting, but it was fun figuring it out anyway.

-LB