Jeremy Campbell
asked on
How to group dates by week and Month in Crystal where the week with most days in a particular month get grouped in that month.
Basically what I'm trying to do is correct the grouping so that the weekly groups are falling into the correct group month.. In other words, if mon-wed falls in the end of January and thur and fri are in February, I want that week to be grouped with January. If only Mon and Tues falls in January and Wed-Fri are February then the week should be grouped with February.
I believe the problem may lie with my formulas as I'm trying to group the weeks by the week ending day of Saturday..
Here is what my formulas are now.
DateGrouping:
{BAQReportResult.LaborDtl. PayrollDat e} +
(7 - DayofWeek({BAQReportResult .LaborDtl. PayrollDat e},crSunda y))
DateGrouping2:
{@DateGrouping} - (DayofWeek({@DateGrouping} , crSaturday)-1)
In group expert the groups are
1. DateGrouping (by Year)
2. DateGrouping (by Month)
3. DateGrouping (by Day, displays as week)
Thanks for the help! Let me know if you need any additional information
I believe the problem may lie with my formulas as I'm trying to group the weeks by the week ending day of Saturday..
Here is what my formulas are now.
DateGrouping:
{BAQReportResult.LaborDtl.
(7 - DayofWeek({BAQReportResult
DateGrouping2:
{@DateGrouping} - (DayofWeek({@DateGrouping}
In group expert the groups are
1. DateGrouping (by Year)
2. DateGrouping (by Month)
3. DateGrouping (by Day, displays as week)
Thanks for the help! Let me know if you need any additional information
Will there be a record for every day of the week ?
ASKER
No necessarily.. Some times the shop will shut down and no transactions will take place during that time.. and usually Sundays there will not be any records.
Ok try this
Create a group on this formula in between Year and Week on the group options tab select use formula as group name and enter the formula
MonthName({@MonthNumber})
//@MonthNumber
WhileReadingRecords;
NumberVar MonthNo := Month({@DateGrouping});
Numbervar WkDay := DayofWeek({@DateGrouping}, crMonday);
If WkDay < 3 Then
If MonthNo <> Month({display_booking.date_created} + (3 - Wkday)) Then
MonthNo := MonthNo + 1
Else If WkDay > 3 Then
If MonthNo <> Month({display_booking.date_created} + (Wkday -3)) Then
MonthNo := MonthNo - 1
Else
Month({display_booking.date_created});
MonthNo
Create a group on this formula in between Year and Week on the group options tab select use formula as group name and enter the formula
MonthName({@MonthNumber})
ASKER
Hmm.. Didn't seem to change anything.. For instance, week ending 4/2/2011 is still being grouped with April.. I'm pretty sure I got your formula set up correctly..
My updated groups right now are
1. DateGrouping (by Year)
2. MonthNumber (by Month) (added Monthname({@MonthNumber}) formula to Group Name formula)
3. DateGrouping (by Day, displays as week)
My updated groups right now are
1. DateGrouping (by Year)
2. MonthNumber (by Month) (added Monthname({@MonthNumber}) formula to Group Name formula)
3. DateGrouping (by Day, displays as week)
Ok so your week group is week ending and not week commencing in that case you will need to change the week formula
and then change the monthnumber formula
({display_booking.date_created} - (DayOfWeek ({display_booking.date_created},crSunday)) + 7)
and then change the monthnumber formula
WhileReadingRecords;
NumberVar MonthNo := Month({@DateGrouping});
Numbervar WkDay := DayofWeek({@DateGrouping}, crMonday);
If WkDay < 3 Then
If MonthNo <> Month({@DateGrouping} + (3 - Wkday)) Then
MonthNo := MonthNo + 1
Else
MonthNo
Else If WkDay > 3 Then
If MonthNo <> Month({@DateGrouping} - (Wkday -3)) Then
MonthNo := MonthNo - 1
Else
MonthNo
Else
MonthNo;
MonthNo
ASKER
That's not the result I get when I use the formulas provided. Can you upload your report with saved data and I will have a look at how you have set it up.
ASKER
That would be easy but it contains a lot of sensitive data.. Is there a way for me to summarize the setup perhaps?
ASKER
Groups are setup as follows:
First Group (Year) looks like this:
Second Group (Month) Looks like this:
In the options the use a formula as Group Name is as follows:
MonthName({@MonthNumber})
The MonthNumber formula is:
WhileReadingRecords;
NumberVar MonthNo := Month({@DateGrouping});
Numbervar WkDay := DayofWeek({@DateGrouping}, crMonday);
If WkDay < 3 Then
If MonthNo <> Month({@DateGrouping} + (3 - Wkday)) Then
MonthNo := MonthNo + 1
Else
MonthNo
Else If WkDay > 3 Then
If MonthNo <> Month({@DateGrouping} - (Wkday -3)) Then
MonthNo := MonthNo - 1
Else
MonthNo
Else
MonthNo;
MonthNo
The DateGrouping Formula is:
{BAQReportResult.LaborDtl. PayrollDat e} +
(7 - DayofWeek({BAQReportResult .LaborDtl. PayrollDat e},crSunda y))
The DateGrouping2 Formula is:
{@DateGrouping} - (DayofWeek({@DateGrouping} , crSunday)+7)
The last group is setup as so:
First Group (Year) looks like this:
Second Group (Month) Looks like this:
In the options the use a formula as Group Name is as follows:
MonthName({@MonthNumber})
The MonthNumber formula is:
WhileReadingRecords;
NumberVar MonthNo := Month({@DateGrouping});
Numbervar WkDay := DayofWeek({@DateGrouping},
If WkDay < 3 Then
If MonthNo <> Month({@DateGrouping} + (3 - Wkday)) Then
MonthNo := MonthNo + 1
Else
MonthNo
Else If WkDay > 3 Then
If MonthNo <> Month({@DateGrouping} - (Wkday -3)) Then
MonthNo := MonthNo - 1
Else
MonthNo
Else
MonthNo;
MonthNo
The DateGrouping Formula is:
{BAQReportResult.LaborDtl.
(7 - DayofWeek({BAQReportResult
The DateGrouping2 Formula is:
{@DateGrouping} - (DayofWeek({@DateGrouping}
The last group is setup as so:
ASKER
Ah, I was able to replicate what you did with your test file.. Sorry, I didn't think about creating an excel file and using that as a source for a test file..
I noticed one issue though.. And this is happening on my report exactly how it happens on your test report.
Have a look at week ending 6/4/2011.. For some reason Tuesday May 31st is getting pulled into week ending 6/4 under the month of May.. (see the attached picture) I'm totally baffled by this. Only thing I can think of is it has something to do with it being the 31st..
Any ideas?
I noticed one issue though.. And this is happening on my report exactly how it happens on your test report.
Have a look at week ending 6/4/2011.. For some reason Tuesday May 31st is getting pulled into week ending 6/4 under the month of May.. (see the attached picture) I'm totally baffled by this. Only thing I can think of is it has something to do with it being the 31st..
Any ideas?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Yup! That did the trick! Thanks for all your time and help! Grouping exactly how I need it now :)