Getting an average across multiple columns 1

[mflahive]

Using CR 9, I am trying to calculate an average amount of a currency field (using only the recent and prior month amounts/2) in a report and I am having a problem getting the prior months amount. The report groups on an "as of" date field and is then formatted to multiple columns to show the amounts over the course of 4 months and the average needs to show once per month for all 4 months. If anyone has an idea for a formula, please let me know...

I hope this makes sense! I'm still new at this.
Thanks.

 

[lbass]

Create three formulas:

//{@reset} to be placed in a group (date, monthly) header_b section (this section can be suppressed):
whileprintingrecords;
numbervar prevsum := 0;

//{@prevsum} to be placed in the details section and suppressed, if you like:
whileprintingrecords;
numbervar prevsum := prevsum + {table.amt};

//{@display} to be placed in the group header_a section:
whileprintingrecords;
numbervar prevsum;

if onfirstrecord then 0 else
(prevsum + sum({table.amt},{table.date},"monthly"))/2

This assumes that you have a group on {table.date} set to print for each month.

-LB