Data count per Week 2

[borisbe]

I'm trying to create a report (crystal 9) and is there a way to sum enrollment on a weekly basis? I have id, start date, end date and enrl data.

id start end enrl
1 01/01/08 01/30/08 5
2 01/03/08 02/15/08 10
3 03/03/08 06/30/08 6

I need a count:

week# enrl
1 15 - includes id 1 and 2
2 15 - includes id 1 and 2
3 15 - includes id 1 and 2
4 15 - includes id 1 and 2
5 15 - includes id 1 and 2
6 10 - includes id 2
7 10 - includes id 2
8 10 - includes id 2

11 6 - includes id 3
and so on

Thanks to anyone who can help me with this report.

 

[IanWaterman]

Group the report by date.

In the Group Insert dialog box, when you select a date field an additional selection box appears with heading section will be printed. From drop down select Week.

Ian

 

[borisbe]

I'm doing something wrong. I decided to look at just 1 particular class

id Start End Enrl
1 01/08/08 03/04/08 12

so was expecting to see

Week# Enrl
2 12 (since it begins in the 2nd week of 2008)
3 12
4 12
5 12
etc.

I took the start_date and created a formula "datepart("ww",{startdate})" to get the week# but now I only get:

I'm getting

Week# Enrl
2 12

I did group by start date, selected "for each week" in the "The section will be printed" and then did a summary of the enrl.

Thanks

 

[lbass]

Do you happen to have a calendar table that contains all dates?

-LB

 

[borisbe]

I just created a calendar table in mysql. It now has a record per day.

CalDate

2008-01-01
2008-01-02
etc.

 

[lbass]

Okay, create a new report using Newtable and link it to Table by linking FROM {Newtable.Caldate} TO {Table.Start} with a left outer join and using a >= join. Link From{Newtable.Caldate} TO {Table.End} again using a left outer and this time using a <= join.

Insert a group on {Newtable.Caldate} on change of week.

Place the {table.ID} field in the detail section and insert a distinctcount on it at the group level.

Note that you should not add any record selection criteria based on Table fields, but you can use criteria on the Newtable field.

-LB

 

[borisbe]

Thanks lbass. I'll give an update as soon as I'm able to try it.

 

[borisbe]

Thanks again lbass. It worked. I've used other formulas that you have posted for other people. You are the best!

 

[borisbe]

The user now wants the times incorporated in the report so distinctcount per hour (so have to see when an hour is within a starttime and endtime) per week# so the comma separated file has more data:

id start_time end_time start end enrl
1 08:30:00 09:30:00 01/01/08 01/30/08 5
2 08:30:00 09:30:00 01/03/08 02/15/08 10
3 10:00:00 13:00:00 03/03/08 06/30/08 6

Week# 8am 9am 10am 11am and so on
1 15 15 0 - includes id 1 and 2
2 15 15 0 - includes id 1 and 2
3 15 15 0 - includes id 1 and 2
4 15 15 0 - includes id 1 and 2
5 15 15 0 - includes id 1 and 2
6 10 10 0 - includes id 2
7 10 10 0 - includes id 2
8 10 10 0 - includes id 2

11 0 0 6 6 - includes id 3
12 0 0 6 6 - includes id 3

Help is appreciated.

 

[lbass]

Create a formula {@null} by opening and saving a formula without entering anything. Then create a separate formula for each hour, such as:

//{@8:30}:
if {table.endtime} >= time(8,30,0) and
{table.starttime} <= time(8,30,0) then
{table.ID} else
tonumber({@null}) //if ID is a string, remove tonumber()

Repeat for each hour. Place these formulas in the detail section and then insert distinctcounts on them at the weekly group level.

-LB