Denominator for Day of Week?

[TechSlinger]

Hello

I am using Crystal 9.0 linking to an Access database.

In a crosstab I would like to have average hospital visits by day of the week i.e. Monday thru Sunday, not actual date of the week. I assume I want the number of visits as a weighted average of distinct days of the week. I have the number of cases but how do I develop a denominator to "weight" the cases by that will take into account the hospital (there is more than one for the report) and distinct number of days of the weeks (so if hospital A for the full year had 1095 visits that came on a Monday they had 3.0 average visits on Mondays (1095/365)).

Thanks very much.

 

[hilfy]

First off, I think your calculation is wrong - you don't have 365 Mondays in a year, you have 52 or 53 (depending on the calendar). This would make your average either 21 or 20.

I don't think you're going to be able to use a crosstab for this (crosstabs don't handle averages well...), but it shouldn't be too difficult to simulate one.

1. Group on hospital.

2. Create 7 formulas to use when counting the number of days of the week in a year. For Monday, it might look something like this (this assumes that you will have every date in the year in your data - let me know if you don't and I'll explain how to do that):

{@MondayForCount}
If DatePart("w", {MyTable.DateField}, crMonday) = 1 then {MyTable.DateField}

3. Create 7 more formulas to do a distinct count of the dates for each day of the week. It will look something like this (NOTE: this distinct count will cover all of the data - it's not limited to the hospital group):

{@MondayCount}
DistinctCount({@MondayForCount})

4. Create 7 more formulas that will be used for counting the number of visits:

{@IsMondayVisit}
If not IsNull(@MondayForCount} then 1 else 0

5. Create a final 7 formulas to calculate your averages:

{@MondayAvgVisits}
//NOTE: Trap for "divide by zero" ahead of time so that we don't get any errors!
if IsNull({@MondayCount}) or {@MondayCount} = 0 then 0
else Sum({@IsMondayVisit}, {MyTable.HospitalGroupField})/{@MondayCount}


6. Now suppress the details and group footer sections, put the Hospital name and the average visits formulas in the group header section.

-Dell

DecisionFirst Technologies - Seven-time SAP BusinessObjects Solution Partner of the Year

http://www.decisionfirst.com