Group by Custom Week 2

[HFTHdfries]

I need to collect and display some data by week (using groups), but I need the weeks to span from Mon-Sun instead of Sun-Sat. I can change the first day of the week inside of formulas, but I can't figure how to tell Crystal to group that way. Also, the first week grouping of this year (should include 12/30 & 12-31 of last year) only includes the dates 1/1/2003 and later. Please let me know if you have any advice. If you need anymore information from me, feel free to ask. Thanks.

Dan

 

[rosemaryl]

Dear HFTHdfries,

This should work and puts sunday in the previous week in the details.

//begin
datevar grpwk;

grpwk := if dayofweek({Table.datefield})= 1 then
(date({Table.datefield})-6) else
(date({table.datefield}-dayofweek({table.datefield}))+2);

grpwk

//end

Group on the above formula and you should have your data grouped by Mondays. I am not at my computer and am doing this from memory so if it doesn't work let me know...

ro
Rosemary Lieberman
rosemary@microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.

 

[HFTHdfries]

Thanks for the response Rosemary. It didn't work but it might be because I've already written the report with regular week groupings and I just tried to change the group to your formula. There's probably something else I need to do. Also, the first two days of this week (12/30 & 12/31 still did not show up under the first week grouping). The second problem with the two days missing is actually the greater problem. I can work around the Monday thing.

Dan

 

[rosemaryl]

Dear HFTHdfries,

When you say it didn't work - what did it do? Error or not give expected results?

As far as the 12/30 and 12/31 that would be handled in the select expert. What needs to be done so that you won't have to modify the report every year is to figure out if the first day of the year starts on a Monday and if not increase the selection criteria to go back to the Monday and include those dates. Give me a bit and I will come up with a selection formula that will do this part.

ro
Rosemary Lieberman
rosemary@microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.

 

[HFTHdfries]

This is my selection formula
//include weeks selected by user
DatePart("ww",{Table.datefield},crMonday,crFirstFourDays) in {?Initial Week} to {?Final Week} and
//include only dates this year
{Table.datefield} in {@StartofYear} to {@LastDate} and
//exclude saturdays & sundays
not(DayOfWeek({Table.datefield},crMonday)in[6,7])

I have set the StartofYear formula to be 12/30/2002 and the LastDate formula is the final date of the last week selected.

Since there are no transactions in our company on Sunday, I was able to leave the grouping on Sunday and just display Monday's date instead of the regular Group Name. However, when I place the datefield in the details, the only dates showing up are 1/1/2003 and later. I need 12/30/2002 and later to show up.

When I used the grpwk formula above, everything looks like it's the same. When I show the formula in the details, it returns Monday's date every time instead of consecutive days.

Let me know if you have any advice.

Dan

 

[rosemaryl]

Dear HFTHdfries,

I just tested the formula and it works fine.

The point of the formula is to group records for Mondays. So yes it will always return monday's date. To see the day of the week of the details, do new formula dayofweek(table.datefield).

Also, to display the correct Monday date in the group tree as well as on the report - customize the groupname (assuming you are using @grpwk) (you can right click/change group/customize group name/formula and enter:

ToText({@GrpWk})

This will display Monday's date as the group name and as such in the Group Tree - otherwise it is confusing to see two different dates.

Now as far as your select formula: First I have to test it in my data so I am doing that now, however this won't pass to the server and as time goes on the report will become a very slow one. You say you have no transactions on Sunday but you exclude Saturday and Sunday from the report. Do you have transactions on Saturdays, but don't want to show them? Wont this skew your data?

Let me double check the point of your select:

Select current year data and if the current year did not start on a Monday then backtrack to include the Monday through 1st day of current year so that "the whole week" in which the first day of the year occurs is shown.

Is that correct? Rosemary Lieberman
rosemary@microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.

 

[HFTHdfries]

You're right, the grpwk formula does work. Thanks for your help on that issue.

You are correct in assessing the point of my select statement. The user selects the weeks they want to view data for and the report should include dates from the previous year if they are part of the first week. I tried to accomplish this using a StartofYear formula in which I can manually set it to the first day of the first week of the year.

I exclude Saturdays and Sundays because the person requesting the report only wants the weekdays displayed (we only work on the weekdays). However, our night crew works until 2am on Saturday so these orders need to be included in Friday's data. I get the daily totals using a subreport that runs each day (in the details field). If the subreport runs for a Friday then it includes the following day's totals.

Right now, there are only three days showing up in the details for the first week of 2003. There should be five including 12/30 & 12/31. Because these days are missing, the daily subreports do not run for those days and I end up missing that data.

Let me know if you figure out how to recover the missing days. Thanks.

Dan

 

[rosemaryl]

Dear HFTHdfries,

Without any parameters entering into the picture here is a select formula that passes the sql and will select back to the monday of the week the year starts in regardless of the year. So the formula selects current year data + whatever days necessary of last year to get the first full week.

Of course the ending should also be adjusted so that it ends on a Sunday...but lets start here:

//begin
({Table.Datefield} in
Switch
(dayofweek(date(Year(currentdate),01,01)) <> 2,
((dateadd(&quot;d&quot;,-(dayofweek(date(year(currentdate),01,01))),(date(year(currentdate),01,01)))+2)
to
Date(Year(currentdate),12,31)),
(dayofweek(date(Year(currentdate),01,01)) = 2), Date(Year(currentdate),01,01) to Date(Year(currentdate),12,31)))
//end

This formula when used now returns data from 12/30/2002 to 12/31/2003.

I was actually writing this when you made your latest post so, now I am trying to understand your latest post, what gets put into the parameters that the users are populating? Are these dates or week numbers?

ro Rosemary Lieberman
rosemary@microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.

 

[HFTHdfries]

The users type in the first and last week numbers that they'd like to include. Our broadcasts codes are all done according to week number so the week number is known by the employees who would use this report.

If I use the select formula written above, what part of my original select formula should I keep? By the way, the Switch function is pretty nice - I've never used it before.

Also, could you give a quick explanation of why my select formula does not include 12/30 & 12/31?

Thanks again,
Dan

 

[rosemaryl]

OK.

As to your formula, from my testing it appears that 12/30 and 12/31/2002 evaluate to week 53 and not to week 1 where they should be. You can test this by placing the following on your report in the details next to the date:


DatePart(&quot;ww&quot;,{Table.datefield},crMonday,crFirstFourDays)

so since that evaluates to week 53 it is not included.

So, I am still sitting here trying to think of how to accomplish what you want by having the users pass the week(s) they are interested in and having the sql pass and retrieve the first days of the week.

I am afraid my brain is not functioning up to par today as I have a bad cold and am taking lots of meds - so give me a bit to fumble through and I should be able to work this out.

The reason I am trying to make sure that the sql passes is that I assume you have more than 1 year of data and if the sql doesn't pass Crystal will sit there and read every record......lots of time.

What is your database and what driver are you using - I might try developing a sql expression that does this.

ro
Rosemary Lieberman
rosemary@microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.

 

[rosemaryl]

Dear HFTHdfries,

Okay, I have worked out a SQL expression to do this! My database is SQL Server and I am using the standard MS SQl Server driver.

The nice thing about using an expression is that this is all processed on the database side and it is &quot;smoking&quot;!

Obviously change the date field in the expression below to your table.datefield.

First create the following SQL Expression. I called it Week.

(case
when {fn WEEK(Incident.&quot;Open Date & Time&quot} = 53 AND {fn YEAR(Incident.&quot;Open Date & Time&quot} = ({fn YEAR({fn NOW()})}-1) then 1
when {fn WEEK(Incident.&quot;Open Date & Time&quot} <> 53 AND {fn YEAR(Incident.&quot;Open Date & Time&quot} = {fn YEAR({fn NOW()})} then {fn WEEK(Incident.&quot;Open Date & Time&quot}
else 0 end)

Now here is your select expert:

{%week} in {?Initial Week} to {?Final Week}

Couldn't be simpler now and this definitely passes. Selects the right dates for whatever year - because a week 53 is rightly part of the 1st week of the next year. I tested this against data that I have and it appears ok - if my logic is correct and I think it is.

Let me know if you need more assistance.

Ro Rosemary Lieberman
rosemary@microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.

 

[HFTHdfries]

Thanks so much for your help Rosemary! It works perfect now and it's much more efficient.

Dan

 

[rosemaryl]

Dear HFTHdfries,

Glad to be of assistance.

This was interesting to work on and I learned something new too (week 53 thing).

ro Rosemary Lieberman
rosemary@microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.

 

[synapsevampire]

Very nice, Ro*!

When dealing with workweeks, keep in mind that SQL Server does NOT use the ISO standard, which loosely translated is that week 1 is whichever week encompasses the first Tuesday of the year (going from memory here).

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[rosemaryl]

Dear SV,

Thanks. I am using SQL 2000 and I just checked with BOL and it states that week 1 is set to whichever week Jan1 occurs in. Only monkey-wrench I saw is that if a year contains 366 days it will actually create a week 54 which means it wouldn't be picked up in my formula.

Years with 366 are leap years and so any year evenly divisible by 4 is a leap year. I don't think the report will be running until 2100 so we don't have to account for Century Years (which are only leap years if evenly divisible by 400!) So, I think the formula needs to be modified so that leap years won't be a problem going forward.

Modified formula to correctly handle leap years:

(case
when {fn WEEK(Incident.&quot;Open Date & Time&quot} >= 53 AND {fn YEAR(Incident.&quot;Open Date & Time&quot} = ({fn YEAR({fn NOW()})}-1) then 1
when {fn WEEK(Incident.&quot;Open Date & Time&quot} < 53 AND {fn YEAR(Incident.&quot;Open Date & Time&quot} = {fn YEAR({fn NOW()})} then {fn WEEK(Incident.&quot;Open Date & Time&quot}
else 0 end)

ro
Rosemary Lieberman
rosemary@microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.

 

[synapsevampire]

Yeah, the MS version of workweek 1 differs from most standards...

Oh well, Bill Loves breaking setting standards

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com