Rolling totals 1

[danausten]

Hi all,

I'm trying to create a report (CR10) that will show a 4 weekly rolling total (total of the 4 most recent weeks). How can I achieve this?

Example data:

Week no cases 4 weekly total
1 10 10
2 10 20
3 10 30
4 10 40
5 20 50
6 10 50
7 0 40
8 0 30
9 0 10
10 50 50

I guess this is not going to be possible in a crosstab?!

Thanks,

Dan Austen

 

[dgillz]

Use a series of if-then statements with the dateadd() function:

{@LastWeek}
if {DateField} in CurrentDate to Dateadd("d",-7,CurrentDate) then {AmountField} else 0

{@Week Before}
if {DateField} in Dateadd("d",-8,CurrentDate) to Dateadd("d",-14,CurrentDate) then {AmountField} else 0

Continue this logic for as many weeks backwards as you would like.

Software Sales, Training, Implementation and Support for Exact Macola, eSynergy, and Crystal Reports

 

[bdreed35]

I have used this logic in a formula in the past to get rolling totals:

numberVar array Accum;
numberVar x;
numberVar y := 0;

if OnFirstRecord then
    ReDim Accum [1]
else
    ReDim Preserve Accum [UBound(Accum)+1];
    
Accum [UBound(Accum)] := {table.Amount}; //Replace with your the field that has your amount field.

If UBound(Accum) < 5 then
    Sum(Accum)
Else
    (
    for x := (UBound(Accum)-3) to (UBound(Accum)) do
        (
            y := y + Accum [x];
        );
    y;
    )

~Brian

 

[danausten]

Thanks Brian, exactly what I was looking for. Even worked in my cross tab!

 

[bdreed35]

I just simplified it a bit. I was able to specify a range of the array to sum. That allowed me to get rid of the for loop.

numbervar array Accum;
numbervar Rolling_Period := 4; //change period number here

if OnFirstRecord then
    ReDim Accum [1]
else
    ReDim Preserve Accum [UBound(Accum)+1];
    
Accum [UBound(Accum)] := {table.amount}; //Replace with your the field that has your amount field.

If UBound(Accum) <= Rolling_Period then
    Sum(Accum)
Else
    sum(Accum[(UBound(Accum)- (Rolling_Period-1)) to UBound(Accum)])

~Brian

 

[danausten]

Thanks, that makes it a lot easier to use. However, I now need to use the formula in a chart but the formula isn't available in the chart expert. Any ideas?

 

[thefox149]

danausten did you ever work out how to put this into a chart
I am using XI and i can't even put this in a xtab

 

[danausten]

thefox149,

Unfortunatly, I haven't found a way to do this in Crystal. In the end I had to create an SQL view but this is by no means ideal.