Average Formula Crystal XI R2

[pungigis]

ok, have a report that returns the following details,

campaign ttl cls bizdays
EOPGRAY 7333 5
EOPGRAY 4311 4
EOPGRAY 3713 5
EOPGRAY 6094 5
EOPGRAY 5335 5
EOPGRSKP 21 5
EOPGRAY 6294 5
EOPGRSKP 85 4
EOPGRAY 3459 4
EOPBEGRY 780 5
EOPGRAY 1819 5
EOPBSGRY 33 5
EOPBEGRY 57 5
EOPGRAY 1500 5
EOPBEGRY 2546 5
EOPGRAY 3379 5
EOPBSGRY 27 5
EOPBSGRY 37 5
EOPGRAY 5012 5
EOPBEGRY 2186 5

I am then grouping on a lastbizdayofweek field and showing the following, on this section I will only be showing the lines with dates.

Wk Begin Ttl Cls Avg Daily Calls
5/23/2010 7333 1467 total calls / biz days in week
EOPGRAY7333 5
5/30/2010 4311 1078
EOPGRAY4311 4
6/6/2010 3713 743
EOPGRAY3713 5
6/13/2010 6094 1219
EOPGRAY6094 5
6/20/2010 5335 1067
EOPGRAY5335 5
6/27/2010 6315 1263
EOPGRSKP21 5
EOPGRAY6294 5
7/4/2010 3544 886
EOPGRSKP85 4
EOPGRAY3459 4
7/11/2010 2599 520
EOPBEGRY780 5
EOPGRAY1819 5
7/18/2010 1590 318
EOPBSGRY33 5
EOPBEGRY57 5
EOPGRAY1500 5
7/25/2010 5952 1190
EOPBEGRY2546 5
EOPGRAY3379 5
EOPBSGRY27 5
8/1/2010 7235 1447
EOPBSGRY37 5
EOPGRAY5012 5
EOPBEGRY2186 5
8/8/2010 8213 1643

Now I need to compare this wk to the last 12 wks above to get an average
Avg Ttl Cls Avg Dly Cls
12 Wk Avg * **

* 5186 (sum of 12 wks above / 12)

** So this SHOULD be (sum of 12 wks above / 58) which is the distinct business days over the 12 week period HOWEVER, because you can have more than one campaign in a week it is putting the business days on each line, I am unable to figure out how to correctly obtain this number, any help would be appreciated.

 

[Madawc]

Add a new group at the lowest level, grouping by weeks. Accumulate values using a running total, this will let you 'evaluate' on change of group.

If you're not already familiar with Crystal's automated totals, see FAQ767-6524.

Madawc Williams (East Anglia, UK). Using Crystal 11.5 with SQL and Windows XP