3 Month Rolling Sum 2

[mdcson]

Hello Everyone,

I am working on a Crystal Report where I need to calculate a rolling sum for 3 months of totals that are summation fields.

Monthly Revenue Ending Balance Days A/R Days at EOM
$54,233,511 $79,748,693 31 -
$54,847,932 $77,915,945 30 -
$55,259,052 $83,792,145 31 Formula Needed

Formula = ((Ending Balance)/((Sum of All 3 Monthly Revenue Amts)/(Sum of all 3 days amts)))

The number I need is the Sum of all 3 Monthly Revenue Amts. Thus, on line three, in this example, I would need the total of $164,340,495 as that piece to place in the formula. I would need this to continue calculating in three month intervals all the way through. For example, in line 4, the numbers of which are not depicted here, I would have the sum of lines 2,3, and 4.

To reiterate, those monthly revenue fields are summation totals placed in a group footer. I tried using the "previous" function but it would not work with the summation fields.

 

[lbass]

Create a formula {@accum} like this for the group footer section and suppress it:

whileprintingrecords;
numbervar array rev;
numbervar array days;
numbervar i;
numbervar j;
numbervar x := 0;
numbervar y := 0;

if onfirstrecord then (
redim rev[1];
redim days[1];

) else
(
redim preserve rev [ubound(rev)+1];
redim preserve days [ubound(rev)+1]
);
j := ubound(rev);
days [j] := sum({table.days},{table.date},"monthly");
rev[j] := tonumber(sum({table.monthlyrevenue},{table.date},"monthly"));
if j < 4 then (
x := sum(rev);
y := sum(days)
) Else
(
for i := j-2 to j do (
x := x + rev;
y := y + days
);
y;
);
y

Then create a second formula to display the result (also in the group footer section):

evaluateafter ({@accum});
whileprintingrecords;
numbervar x;
numbervar y;
{@endingbalance}/(x/y)

I worked this out in a detail section and then adjusted it for a group footer section, so haven't fully tested it. This formula is adapted from one by bdreed.

If you don't want the calculation to display in the first two group footers, use a suppression formula like this:

groupnumber < 3

...assuming you only have the one monthly group.

-LB

 

[Madawc]

I think you could do it using three variables, call them T1, T2 and T3. Set all three to zero at the start. For each month, move the value in T2 to T3, move the value in T1 to T2, and move the monthly total to T1. Sum them to get your rolling 3-month total. To avoid showing it on the first two months, suppress it if the month is less than two months before the current date, or else have a running total that counts 1 per group.

I suggest that you create a test section to display the working fields, tidying up to get the final form only once you are sure everything works.

The method assumes you have something for each month: if this is not so, you will need to use DateDiff and maybe store the month / year in another variable.

The use of Crystal's totals is outlined at FAQ767-6524.


Madawc Williams (East Anglia, UK). Using Crystal 11.5 with SQL and Windows XP

 

[mdcson]

Thank you Ibass! It worked perfectly!