Solved

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.

Posted on 2011-09-28
13
322 Views
Last Modified: 2012-05-12
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.PayrollDate} +
(7 - DayofWeek({BAQReportResult.LaborDtl.PayrollDate},crSunday))


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
0
Comment
Question by:SeyerIT
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 6
13 Comments
 
LVL 19

Expert Comment

by:GJParker
ID: 36716881
Will there be a record for every day of the week ?
0
 
LVL 2

Author Comment

by:SeyerIT
ID: 36716905
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.
0
 
LVL 19

Expert Comment

by:GJParker
ID: 36717196
Ok try this

//@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

Open in new window


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})

0
Is Your DevOps Pipeline Leaking?

Is your CI/CD pipeline a hodge-podge of randomly connected tools? You’ve likely got a tool to fix one problem & then a different tool to fix another, resulting in a cluster of tools with overlapping functionality. Learn how to optimize your pipeline with Gartner's recommendations

 
LVL 2

Author Comment

by:SeyerIT
ID: 36717346
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)

0
 
LVL 19

Expert Comment

by:GJParker
ID: 36717851
Ok so your week group is week ending and not week commencing in that case you will need to change the week formula

({display_booking.date_created} - (DayOfWeek ({display_booking.date_created},crSunday)) + 7)

Open in new window


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

Open in new window

0
 
LVL 2

Author Comment

by:SeyerIT
ID: 36718866
Well that made a change for sure, still not quite right though..
Currently it looks like this:
 Current Week Grouping
0
 
LVL 19

Expert Comment

by:GJParker
ID: 36813334
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.
0
 
LVL 2

Author Comment

by:SeyerIT
ID: 36814481
That would be easy but it contains a lot of sensitive data.. Is there a way for me to summarize the setup perhaps?
0
 
LVL 2

Author Comment

by:SeyerIT
ID: 36814613
Groups are setup as follows:
 Groups are setup as follows
First Group (Year) looks like this:
Year
Second Group (Month) Looks like this:
 Month
In the options the use a formula as Group Name is as follows:
 Month Group Name OptionsMonthName({@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.PayrollDate} +
(7 - DayofWeek({BAQReportResult.LaborDtl.PayrollDate},crSunday))


The DateGrouping2 Formula is:
{@DateGrouping} - (DayofWeek({@DateGrouping}, crSunday)+7)

The last group is setup as so:
 Day Grouping
0
 
LVL 19

Expert Comment

by:GJParker
ID: 36814876
Here's the test report and data I have setup
date-range-test.rpt
Dates.xls
0
 
LVL 2

Author Comment

by:SeyerIT
ID: 36815985
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?

 Grouping issue
0
 
LVL 19

Accepted Solution

by:
GJParker earned 500 total points
ID: 36816027
I made a change to teh @MonthNumber Formula befoe and forgot to reset the test, change your MonthNumber Formula to

WhileReadingRecords;
NumberVar MonthNo := Month({Sheet1_.Date});
Numbervar WkDay := DayofWeek({Sheet1_.Date}, crSunday);

If WkDay < 4 Then
    If MonthNo <> Month({Sheet1_.Date} + (4 - Wkday)) Then
        MonthNo := MonthNo + 1
    Else
        MonthNo
Else If WkDay > 4 Then
    If MonthNo <> Month({Sheet1_.Date} - (Wkday - 4)) Then
        MonthNo := MonthNo - 1
    Else
        MonthNo
Else
        MonthNo;

If MonthNo <> 0 Then MonthNo Else 12

Open in new window

0
 
LVL 2

Author Closing Comment

by:SeyerIT
ID: 36816053
Yup! That did the trick! Thanks for all your time and help! Grouping exactly how I need it now :)
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Written by Valentino Vranken. A while ago I wrote an article called Chart Optimization Tips (http://www.experts-exchange.com/articles/Microsoft/Development/MS-SQL-Server/MS-SQL_Reporting/Chart-Optimization-Tips.html).  This article explained how …
Have you ever had to extract data from a Microsoft SQL Server database and export it to an Excel file, but did not want to use a DTS package? The concept in this article is not new, but it is the answer and will also work on 64-bit SQL boxes.   …
There are cases when e.g. an IT administrator wants to have full access and view into selected mailboxes on Exchange server, directly from his own email account in Outlook or Outlook Web Access. This proves useful when for example administrator want…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question