sum on a date var range

[fancyface]

Hi there. How do I do this? first of all I'm using Crystal 7 - limited I know. No way around it. In my record selection I need 4 years of giving dates. No problem. I need one running total that sums the last year of giving one year back from today's date. No problem. But then if that running total is zero, I want to take the most recent gift date within that 4 years and sum back 1 year from that date. I need both sums in the same report.

Racking my brain here. Any help would be appreciated. Thank you.

 

[Madawc]

What have you tried?

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

 

[Charliy]

What you need is 3 formulas:
1 to get the total for the last year
1 to get the total for the last 4 years
and 1 to pick which of the previous 2 to use, s omething like:

IF {@LASTYEAR} = 0
THEN {@FOURYEARS}
ELSE {@LASTYEAR}

 

[fancyface]

Charliy - I don't want the total of the last 4 years, I want the most recent 12 month total within that 4 years.

 

[lbass]

Please don't start multiple threads on the same topic.

Try something like this:

//{@reset} to be placed in the group header for donor:
whileprintingrecords;
numbervar amt;
if not inrepeatedgroupheader then
amt := 0;

//{@accum} to be placed in the detail section:
whileprintingrecords;
numbervar amt;
if {table.date} in dateadd("yyyy",-1, maximum({table.date},{table.donor})) to maximum({table.date},{table.donor}) then
amt := amt + {table.amt};

//{@display} to be placed in the donor group footer:
whileprintingrecords;
numbervar amt;

-LB

 

[fancyface]

I don't have date add in version 7

 

[lbass]

I would just change it to something like:

whileprintingrecords;
numbervar amt;
if {table.date} in maximum({table.date},{table.donor})-365 to maximum({table.date},{table.donor}) then
amt := amt + {table.amt};

-LB

 

[fancyface]

Thank you - will that add to itself only if there are 2 lines per key? Do I put that in the details section so it will calculate for every detail line, i.e. every row per key?

 

[fancyface]

so far so good - thank you. Will look at it again MOnday. Thank you everyone for the help thus far - have a great weekend.

 

[lbass]

Just follow the previous instructions--you still need the other two formulas, too.

-LB