Week by Week Comparison

[borisbe]

My data:

year id stat_date

2008 1 01/01/2008 - this is a date in week 1 of 2008
2007 2 01/08/2007 - this is a date in week 2 of 2007

2008 1 02/04/2008 - this is a date in week 6 of 2008
2007 3 02/06/2007 - this is a date in week 6 of 2007

I need to compare week to week (id distinctcount) to see what happened from a previous year to a newer year. Problem: Some weeks in a year might not have data so the user wants to see the distincountid of 0.

What I have now:

Year Week# idcount
2008 1 1

2007 2 1

2008 6 1
2007 6 1

What user wants to see:

Year Week# idcount
2008 1 1
2007 1 0

2008 2 0
2007 2 1

2008 6 1
2007 6 1

I do have a calendar table that includes dates (i.e. 2008-01-01) if that is needed.

I have Crystal 9 using a comma separated file.

Help is appreciated.

 

[lbass]

You will need to use the calendar table to create your groups. Group #1 should be on week# (datepart("ww",{calendar.date}), and group #2 should be on {calendar.date} (on change of year). Then you can insert a distinctcount on ID at the group #2 level and suppress the detail section.

When joining the tables, use a left outer join FROM the calendar table TO the date in your current table. Do not add any record selection criteria to the right hand table. You can, however, limit records from the Calendar table.

-LB

 

[borisbe]

Sorry lbass, I forgot that user wanted it also grouped by an area so I had to get a different csv file with area. They want it grouped by area then week# so I'm not sure how to incorporate the area to your groups.

I don't know if this is a problem. But the year field in the CSV file is a character data type so the report doesn't recognize it as the year part of a date.

Year Week# idcount area
2008 1 1 ART

2007 2 1 ART

2008 6 1 ACT
2007 6 1 ACT

User wants it grouped by area then week# then year:

Year Week# idcount subject
2008 6 1 ACT
2007 6 1 ACT


2008 1 1 ART
2007 1 0 ART

2008 2 0 ART
2007 2 1 ART

It's been a hard week so I really appreciate the time you have taken to help me.

 

[lbass]

Did you try just adding a group on area and then going into the group expert and making area the group #1? If so, what problems arose? I'm assuming the area file links to some field in the file that contains the ID.

-LB

 

[borisbe]

I'm probably just not understanding what I need to do in the report so here are my steps:

1. calendar_date table joined to csv file ("calendar_date" field left equal joined to "stat_date")

2. Groupings

1st group: Area
2nd group: datepart('ww',{calendar_date})
3rd group: calendar_date (the section will be printed for each year).

3. Selected id in the detail section and used insert, summary, distinctcount at 3rd group (suppressed detail section)

4. Results showing:

Year Week# IDcount Area
2008 1 1 blank
2007 1 1 blank

2008 2 1 blank
2007 2 1 blank
it has 53 week # groups like the 2 above then the area (ACT) begins:

Year Week# IDcount Area

2008 6 1 ACT
2007 6 1 ACT

2008 1 1 ART

2007 2 1 ART

I hope I'm not wasting your time because I'm not understanding what I need to do in the report. Thanks again.

 

[lbass]

You can eliminate the blank groups, but are you saying that once the areas appear that not all weeks appear within the area?

-LB

 

[borisbe]

When a year doesn't have data for an area for a week# then it doesn't show so

What it is showing now:

Year Week# IDcount Area

2008 6 1 ACT
2007 6 1 ACT

2008 1 1 ART

2007 2 1 ART

What I need it to show:

Year Week# IDcount Area
2008 6 1 ACT
2007 6 1 ACT

2008 1 1 ART
2007 1 0 ART - this data still isn't showing because the csv file has no data for week 1 for 2007

2008 2 0 ART - this data still isn't showing because the csv file has no data for week 2 for 2008
2007 2 1 ART

Thanks for still trying to help me.

 

[lbass]

Did you add the new file with a left outer join? Please explain what table you are joining to what table and on what field.

If you delete the area group, do you get all years and weeks?

-LB

 

[borisbe]

They have me on other reports that are taking priority so as soon as I can then I'm getting back to this one. I'll post another message as soon as I can. I really appreciate your help, lbass.