threshold date

[atthevilla]

Oracle 10g, Crystal 10.
For fundraising, I would like to capture the date when a donor has reached a threshold of $10,000 within a fiscal year. There may be multiple gifts from this same donor:

06/01/2008 $5,000
07/01/2008 $4,000
10/01/2008 $1,000
12/01/2008 $2,000

The pertinent date here is 10/1/2008 since that is the date when he arrived at the $10,000 level, all subsequent gifts do not matter. I want to have a crosstab type report with a count of how many donors 'arrived' at this level in a given month and not count them again for the remaining months of the year. I'm thinking running totals but I don't know how to work it.

 

[Madawc]

Do a running total, but test it in a formula. If months-so-far is over 10,000, subtract the monthly total for months-so-far. If the result of the subtraction is less than 10,000, this was the month they crossed the threshold.

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[lbass]

Insert a group #1 on donor and a group #2 on month. Then create a formula like this to be placed in the month group footer and suppressed:

whileprintingrecords;
numbervar curr;
numbervar prev := curr;
datevar monthx;
numbervar jan;
numbervar feb;
numbervar mar;
numbervar apr; //etc. through dec

curr := curr + sum({table.donation},{table.date},"monthly");
if prev < 10000 and
curr >= 10000 then
monthx := {table.date} else
monthx := date(0,0,0);
if month(monthx) = 1 then
jan := jan + 1;
if month(monthx) = 2 then
feb := feb + 1;
if month(monthx) = 3 then
mar := mar + 1;
if month(monthx) = 4 then
apr := apr + 1; //etc.
monthx

Create a reset formula for the donor group header:

whileprintingrecords;
numbervar curr;
numbervar prev;
datevar monthx;
if not inrepeatedgroupheader then (
curr := 0;
prev := 0;
monthx := date(0,0,0)
);

Then create a manual crosstab in the report footer, using a separate formula for each month like:

//{@jan}:
whileprintingrecords;
numbervar jan;

//{@feb}:
whileprintingrecords;
numbervar feb;

//etc.

-LB