Cross Tab Averages

[shelby55]

Hi

I am using Crystal 9.0 reporting off an Access 2003 database.

I have a cross tab with a row value of hour of day and a column value of day of week. Both values are based on the visit date/time.

The current summary field is count of chart number and everything is good. BUT I would like to show averages so how would I do that...just selecting the average summary makes all fields revert to 1.0 which is incorrect.

Thanks.

 

[lbass]

Average of what? Are you looking for the average per hour across days of the week? Or average per dayofweek across hours? It seems like you would be looking for the average at either the row and/or the column totals.

-LB

 

[shelby55]

HI LB

Thanks for the response. I'm looking for the average at the row and column totals.

Thanks.

 

[lbass]

This would be simpler just using a manual crosstab. Insert a group on a formula:

time({@table.datetime})

...on change of hour. Create one formula per day of week:

//{@Sun}:
if dayofweek({table.datetime}) = 1 then 1

//{@Mon}:
if dayofweek({table.datetime}) = 2 then 1

//etc.

Place these in the detail section and insert sums (not counts) on them at the group level, drag the groupname into the group footer, and then suppress the detail section and group header. For the averages, create one formula per column, e.g.:

sum({@Sun})/distinctcount({table.datetime},"by hour")

Then for the row averages, just use:

(sum({@Sun},{table.datetime},"by hour")+sum({@Mon},{table.datetime},"by hour")+...+sum({@Sat},{table.datetime},"by hour"))/7

-LB