Using Formula to Forecast a Value

[Chinxster]

I'm trying to forecast for the next month using moving averages. Basically, the information from the last 12 months are averaged out. In my Crystal Report so far I have 13 columns of information. I have 12 columns that hold the qty data for each month of the year. The 13th column shows the year. How do I average the 12 months of data by corresponding year? I tried the formula Average(Array(the field names for the 12 months)), but I think the report was trying to average all the columns of data, not the rows. I need each row of data(from Jan to Dec) to be averaged according to the correct year. I need the average of all the data from each month for the year 2002 and 2003. Below, shows the data I have in my report so far. How do I average each row with a formula? Any help with this would be greatly appreciated. If you need more info, please reply. I thank you so much!!

Year Jan Feb Mar Apr June July Aug Sept Oct Nov Dec
2003 2 3 1 2 1 0 5 10 1 2 2
2003 3 4 5 1 1 2 0 5 2 1 1
2002 1 7 1 1 1 0 2 3 1 5 0

Glendy

 

[pgtek]

Hi create a formula that sum the row
Avg({your field})
then in your report expert
select on group


Sum(@yourformula,year)
this would chage on the year incremented

cheers

pgtek

 

[lbass]

Maybe this will help. It looks like you have a manual crosstab here, and I'm guessing that you have grouped by year and that each year represents a group header or footer with summaries from a suppressed detail section. There are two formulas you could use to capture the average value. If you have a manual crosstab, you will have 12 formulas defining your columns. You could use the following {@avgarray}:

average([{@Apr},{@Aug},{@Dec},{@Feb},{@Jan},{@Jul},{@Jun},{@Mar},{@May},{@Nov},{@Oct},{@Sep}])

Place this in the details section and then insert a summary on it. With this approach, each formula will be counted in the denominator to create the average, so if you have only 10 months of data you would want to eliminate the extra two months from the array formula. This formula will also count months with "0" data in the denominator, unless you (somehow) correct for it.

Another approach would be the following formula {@yearlyavg}, to be placed in the group footer or header:

sum({table.amount}, {table.date}, "annually&quot/distinctcount({@month date},{table.Date},"annually&quot

This assumes that you have grouped on {table.date} "annually" and that the formula {@monthly date} is:

month({table.date})

This will give you averages for months which have data (excluding 0 sums from the count because there are no {table.dates} within that month).

-LB