smart formula

[Regelos]

I have one more question this is a report I walked into and it took me long enough to figure out the report logic that was written. What it does now is take 4 week forcast from this long field converts it then figures out the current week (based off current month) steps foward that many places and grabs the next 4 numbers add's them up and divides them for avg. This is now working fine with a few tweeks.

but now they want 13 weeks (then divided by 3 for a monthly avg) and before end of year not having data was o.k. now its not. we have a field that holds the year only and a field that has the long values (keyed off the year)

any idea how to roll the 2 together?

in CommaDelimitedForecast
Replace ({fcs_sum.fcs_pr_fcst},';',',') + ','

in ForecastArray
Global numberVar array Forecast;
numberVar ArraySize := 52;
numberVar i := 1;
numberVar j := 1;
numberVar k := 1;

ReDim Forecast[ArraySize];

for i := 1 to ArraySize do
(
k := InStr (j,{@CommaDelimitedForecast},",");
Forecast := ToNumber(Mid ({@CommaDelimitedForecast},j,k-j));
j := k + 1
);

0

in ForecastWeekNumber
DateDiff ("w",Date (Year (CurrentDate),1,1),CurrentDate)+1

in ForecastYear
ToNumber(Year(CurrentDate))

in FourWeekForecast
WhileReadingRecords;

Global numberVar array Forecast;
ReDim Preserve Forecast[52];

Forecast[{@ForecastWeekNumber}]+
Forecast[{@ForecastWeekNumber}+1]+
Forecast[{@ForecastWeekNumber}+2]+
Forecast[{@ForecastWeekNumber}+3]

in SharedForecast
whileprintingrecords;
shared numbervar fcst := {@FourWeekForecast}

 

[Regelos]

anyone have any ideas?

 

[Madawc]

It's not clear what you're asking.

If you want to show something for a week that has no data, there is no convenient answer. If you're allowed to add a table to the database, you could do a table of weeks and do a left-outer to the data. That way you'd get zero totals.

If you can't add a table, you'll be stuck with creating a 'Mock Crosstab'. This is something that looks like a Crosstab, but in fact you define each column yourself, normally as a running total. This would need to go in the report footer, because running totals count as the reports 'run' and they will not be complete until then.
Crystal should have included an example along with the Crosstabs. You can save a little time by doing a paste to a dummy report, changing the name and then pasting back.

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10