Formula referencing itself

[mtrang81]

Hello all,

I'm converting an EXCEL projections report into Crystal 9.0. My report has these fields for 12 records (for the 12 months): 2004 Actual, Monthly Relativity (which is 10-yr monthly total/total for previous 10 years), YTD 2004 Actual and 2004 Monthly Projection, etc.

Most of these fields are formulas except for 2004 Actual. My current report is in EXCEL and works great. However, Crystal is giving me errors.

Here's some of the sample data:
2004 Actual 2004 YTD Actual
Jan 4,151 4,151
Feb 4,496 8,647
Mar 6,099 14,746
Apr 6,252 20,998
May 5,945 26,943
Jun 6,659 33,602
Jul 6,302 39,904
Aug 6,210 46,114
Sep 5,948 52,062
Oct null
Nov null
Dec null

The logic for my projection calculations is:
1) calculate YE Projection: If 2004 Actual is null, then print previous YE Projection, else YTD 2004 Actual/Sum of Monthly Relativity

So for September, my YE Projection is 68,493 and it should be the same for Oct, Nov, & Dec since they don't have 2004 Actual # yet.

2) calculate Monthly Projection: If 2004 Actual is null, then YE Projection * Monthly Relativity, else print 2004 Actual.

So for September, my Monthly Projection is 5,948 but for October, it should be 6,016 (68,493*.08)

I can't get step #1 above to work properly because my formula has to reference itself in order to print its previous value.

Is there any way around this?

Any suggestion would be greatly appreciated.
Thans in advance,
Trang

 

[Madawc]

That's a feature of Crystal, which is a sophisticated report-writing tool rather than a true language. Often it is necessary to have several formula fields, referencing each other or else doing the same thing.

In Crystal 8.5, you can cut-and-paste to a dummy Crystal report as a short-cut to making large numbers of very similar fields. Change the name on the dummy and then copy back.

Madawc Williams (East Anglia, UK)