Daily Attendance Averages by Month w/in FY 2

[bmacdo]

Greetings,

Using Crystal 10, I am to create daily averages of students in our programs by month within fiscal year. Attendance data is available in a single view: Client ID, Admission date and Exit date. The report will be by fiscal year, but not necessarily the current one.

The report model looks like this:

7/07 8/07 9/07 10/07 etc.
---------------------------------------
facil1 31 41 35 23
facil2 22 23 21 20
facil3 36 32 35 35
---------------------------------------
total pgm1 89 96 91 78

I have a report that displays counts of attendees by day within specified 31 day range, but done somewhat manually, i.e. it has formulas for each day in the range. I would appreciate some advice to start with this broader request.


Brad Macdonald

 

[MrBillSC]

If the above example is all you need for a year you might try using a cross tab report (use the cross tab wizard).

Into the "columns" field move the attendance date field.
Click the "group options" button below the columns field and in "the column will be printed" field select "for each month".
Into the "rows" field move the facility field.
Into the "summarized" field move Count of student Id.

MrBill

 

[lbass]

Your current formula for the daily attendance probably looks something like this:

if {table.admissiondate} <= [hard date here] and
{table.exitdate} >= [same date here] then 1

Then you sum the formula for the day. To average this for a particular month, you would need to determine the number of class days per month, and then divide the sum of the above formula by that. It might help to look at Ken Hamady's formula for calculating business days, which I think could be extended to this problem.

I guess I'm not totally clear on the issue. If you want to sort of automate this, you probably should create a table that contains the class dates, and then link it to the table containing the admission and exit dates, using >= and <= links. I think you could then insert a crosstab to get the desired results.

-LB

 

[bmacdo]

The students reside with us, so luckily I don't need to worry about week days vs. weekend days. They would be considered in attendence from admission date to exit date, regardless of class schedule.

You're right, LB, in that in the current 31 day report, the field for each day is counted after the student row displays an "X" for a given day, based on the number of days to 31 from the parameter report date and provided the student hasn't exited.

What I am unclear about is how to automate this to not go from 31 to up to 365 days of parameter fields, but rather average by month dynamically and be able to report on a current FY that is incomplete.

Thanks much for your input, Mr. Bill and LB!

Brad

 

[lbass]

Are you able to create a table that you could link to with <= and >= joins?

-LB

 

[bmacdo]

I'm going to ask the system administrator today if I can, or if there is already a calendar file in existence. I haven't found one.

If I can or if there is an existing one, how would I link to it for the days besides the admission or exit dates? I guess it's how to create and use attendence dates between the admission and exit dates that I know are in the file that I am not getting my head around. Thanks ...

Brad

 

[lbass]

My thought was that you could link FROM the new table to the dates table with a left join, and set the join types like this:

NewTable.Date >= OldTable.Admission Date

NewTable.Date <= OldTable.ExitDate

Then you could use NewTable.Date as your column field in a crosstab and set it to on change of month. I'm not absolutely sure this would work, but it seems like it might.

-LB

 

[bmacdo]

That's what I thought you meant after I fired off my last question. I really appreciate your help. And I hope I can get that calendar table.

Brad

 

[bmacdo]

I got my calendar table today and a fine thing it is, working just like I'd hoped, giving me one record per day of enrollment per attendee, etc.

However, not only would I like to retrieve records according to the join you suggested (NewTable.Date >= OldTable.Admission Date + NewTable.Date <= OldTable.ExitDate), but also those that have null value for OldTable.ExitDate ...

Do I have to leave out the second join fpr NewTable.date <= Oldtable.ExitDate and take care of that in the select window? Thanks again for any advice.

Brad

Brad

 

[lbass]

I'm not sure, but it sounds like it could work. Why not try it?

-LB

 

[bmacdo]

my average daily population saga continues ...

Is there any way to use different summary types between the row display and total display in the same crosstab, or must I do two crosstabs separately or one manually? I want averages by month in rows and a total of averages in the column total line.

Brad Macdonald