Working with group totals 1

[DuncanSutcliffe]

Think I'm having a "can't see the wood for the trees" moment here.

I'm grouping on a field which indicates what year my data relates to, then summing the numerical data that's in it. I'd like to take the total from the first group and last group and compare them in the report footer (subtract one from the other or whatever).

How can I create a formula with the totals from the first group and last group? I don't know in advance how many times the group will be iterated.

Using CR8.5.

TIA
Duncan

 

[IanWaterman]

You can do this using variables

@Year Totals
whileprintingrecords;

global numbervar Yearmax;
global numbervar Yearmin;

If sum(fieldname, groupfield) > Yearmax then Yearmax:= sum(fieldname, groupfield);
If sum(fieldname, groupfield) < Yearmin then Yearmin:= sum(fieldname, groupfield);

You can use this variables in your report footer to do what ever calculations you like.

In report header intialise Yearmax:=0 and YearMin:=999999999
ie some enormous figure which will not be achieved in a year.

Ian

 

[lbass]

You could use two running totals. The first one {#firstyear} would sum {table.amt}, evaluate based on a formula:

year({table.date}) = year(minimum({table.date}))

Reset never. Repeat for the second running total {#lastyear} using the following formula for evaluation:

year({table.date}) = year(maximum({table.date}))

Then create a formula {@diff}:

{#lastyear} - {#firstyear}

-LB

 

[DuncanSutcliffe]

Running totals, of course! Use 'em about once a year, forget they're even there...

I'm not actually grouping on a date field, but on a {year_id} that looks up a {year_description} in another table. So I don't need even to extract year from a field.

Thanks,

Duncan