12 Month Rolling Total 1

[Ozweepey]

I have a report that lists 5 years worth of data. In a column next to the data I want to show a rolling 12 month sum.

Ex.

Month Amount Running
1/31/99 1 1
2/28/99 2 3
3/31/99 3 6
4/30/99 4 10
5/31/99 5 15
6/30/99 6 21
7/31/99 7 28
8/31/99 8 36
9/30/99 9 45
10/31/99 10 55
11/30/99 11 66
12/31/99 12 78
1/31/00 13 90

The running sum in 1/31/00 is only for the previous 12 months not the total. I have over 5 years worth of data and we use the 12 month rolling as a benchmark. Any help would be greatly appriciated. I'm using CR 9.0.

Thanks,
Ozweepey

 

[synapsevampire]

Insert a Running Total field and select reset evaluate use a formula year({table.month}) <> previous(year({table.month}))

Should work.

Otherwise just use a variable to keep a total ands use the same previous() function to reset it at the year.

-k

 

[Ozweepey]

Thank you for your quick response. I tried the following formula:

{tblBacklog.Date}<>previous(year({tblBacklog.Date}))

and I get an error message saying that a field is required in the year function. Am I misunderstanding your reply? Should I be pulling the month out of the date field in my table?

Thanks,
Ozweepey

 

[bdreed35]

If I am understanding the requirements correctly, each month displayed should have the total for the last 12 months. Once you get to 1/31/00, the Running column should have the totals for 2/28/99 thru 1/31/00 and 2/29/00 should have the totals for 3/31/99 thru 2/29/00. If this is the case than try this:

Create this formula and place it in your section where you would display the Running field:

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) < 13 then
    Sum(Accum)
Else
    (
    for x := (UBound(Accum)-11) to (UBound(Accum)) do
        (
            y := y + Accum [x];
        );
    y;
    )

~Brian

 

[Ozweepey]

Thanks bdreed35! It worked great. But how do I chart the results? I'm a self taught crystal user and I am having a hard time with the variables. Thank you for your help.

Ozweepey

 

[lbass]

bdreed,

I thought this was a very cool solution (*).

Ozweepey,

I might be wrong about this, but I don't think you can chart on variables (at least not in 8.0). Here's a case where you might need to export to Excel and create the chart there--but perhaps someone will disagree with me. I tested Brian's solution and tried to chart on it, and the formula appears in the window for selection, but the arrow keys indicating it's available for the &quot;on change of&quot; or &quot;show fields&quot; are grayed out.

I think it's important to indicate up front if your goal is charting, because there are limitations like this and in some cases other methods might be suggested, although I think variables are probably the only solution in this particular case.

-LB