Week Of The Month 1

[Bennie47250]

Using Crystal 7.0 and MS SQL Database

Need help with a formula that determines the week of the month and displays the results as Month Week.

Example of what I’m looking for:

Date Result

8/30/04 Aug week 5
9/15/04 Sept week 3
10/4/04 October week 1

Thanks

 

[synapsevampire]

The formula is something like the following for the week of the month:

day(cdate(year(dateadd("m",1,{table.date})),month(dateadd("m",1,{table.date})),1)-1)/7

So the display formula would be

monthname(month({table.date}))+ " week "+ day(cdate(year(dateadd("m",1,{table.date})),month(dateadd("m",1,{table.date})),1)-1)/7

To obtain the DateAdd function for Crystal reports 6 and 7, you can download the additional function called Ufldateadd.exe, from the Crystal Decisions support site at:

http://support.crystaldecisions.com/downloads

Referenced from:

http://support.businessobjects.com/communityCS/TechnicalPapers/scr567_datetimefunctions.pdf

-k

 

[huggyd]

Not sure of exact code but use the day() function to extract the day element and divide by 7, round up to nearest whole number

 

[synapsevampire]

Oooops, yeah, I showed the week for the end of the month

Use:

day({table.date})/7

I was a tad ambitious

-k

 

[lbass]

Try:

monthname(month({table.date})) +" Week "+ totext(Datepart("ww",{table.date})+1
- Datepart("ww",({table.date} - Day({table.date})+1)),0,"")

-LB

 

[Bennie47250]

lbass

Where is the monthname coming from?

 

[synapsevampire]

LB: WW doesn't show the week of the month, it shows week of the year.

Should be:

monthname(month({table.date}))+ " week "+ totext(int(day({table.date})/7)))

-k

 

[synapsevampire]

Ooops again...

monthname(month({table.date}))+ " week "+ totext(int(day({table.date}-1)/7))+1)

Looks OK, obviously I'm unable to test right now...

-k

 

[lbass]

SV - I know. You must not have tried my formula, which does work for week of the month.

Bennie--

If you don't have monthname available to you, try:

totext({table.date},"MMMM") +" Week "+
totext(Datepart("ww",{table.date})+1
- Datepart("ww",({table.date} - Day({table.date})+1)),0,"")

-LB

 

[Bennie47250]

I appreciate all the suggestions, but I have not been able to get them to work yet.

Please remember that I'm on version 7.0.

Apparently datepart is something added in a later version. I have searched the CD site and don't see that this is an UFL I can download and update my version with.

Was Monthname also something added in a later version?

 

[lbass]

Bennie,

Are you looking for the week of the month according to the calendar and with the week starting on Sunday? That was what my solution would have given you, so that October 4, which is a Monday, would have been week 2, since October 1 fell in the previous Sunday - Saturday week. Or, do you just want to call October 1 to 7 week 1, October 8 to 14 week 2, etc.?

-LB

 

[Bennie47250]

Question: Are you looking for the week of the month according to the calendar and with the week starting on Sunday?

Answer: Yes

Question: ...so that October 4, which is a Monday, would have been week 2, since October 1 fell in the previous Sunday - Saturday week.

Answer: Yes, exactly

 

[synapsevampire]

Sorry, I misunderstood.

-k

 

[lbass]

If you have the truncate, dayofweek, and day functions, the following should work:

totext({table.date},"MMMM") + " Week " +
totext(truncate(({table.date} -
(date(year({table.date}),01,01) -
dayofweek(date(year({table.date}),01,01))+1))/7)+2 -
(truncate((({table.date} -
day({table.date})+1) -
(date(year({table.date}),01,01) -
dayofweek(date(year({table.date}),01,01))+1))/7)+1),0,"")

-LB

 

[Bennie47250]

lbass, you are the best!

This is close but I think there is still a small issue.

This formula shows 8/30/04 as August week 5 but it also shows 9/1/04 as September week 1 when it should be in August week 5.

The Oct 1 and 4 days you asked about earlier are working correctly.

How can the last formula be tweaked to work correctly?

 

[synapsevampire]

<chuckling>

This thread is purdy funny. Nicely done, LB, your code is always well thought out.

I've never seen such business rules... Might toss out a few examples so that the world understands why 9/1 is in August... Are you including everything thru Sunday in the previous months week for partial weeks?

Consider creating a table with all dates with attributes for each such as month number, fiscal month, etc. I don't do this sort of thing in CR, it's standard fare in big kid data warehouses, such as Holidays, business days, etc:

faq767-4532

Read this, and then read some of Ralph Kimball's stuff on data warehousing and implementing Business Intelligence as well as OLAP architecture.

Embedding such rules in client tools will mean high maintenance.

-k

 

[lbass]

Well, that's because 9/1 IS in the first week of September. If you have a rule about the weeks that cross months, what is it? Is the rule that all days in a week belong to the month of the first day of that week? If so, try the following:

totext({table.date}-dayofweek({table.date})+1,"MMMM") + " Week " +
totext(truncate(({table.date}-dayofweek({table.date})+1 - (date(year({table.date}),01,01)
- dayofweek(date(year({table.date}),01,01))+1))/7)+2 -
(truncate((({table.date}-dayofweek({table.date})+1 - day({table.date}-dayofweek({table.date})+1)+1) -
(date(year({table.date}),01,01) -
dayofweek(date(year({table.date}),01,01))+1))/7)+1),0,"")

-LB

 

[Bennie47250]

lbass,

Sorry but I thought you understood that whatever month the week started on, all the dates in the 7 day period would be considered that week.

When you asked the question "Are you looking for the week of the month according to the calendar and with the week starting on Sunday?" I thought we were on the same page.

So back to my example of the week started on Sunday 8/29/04 as the 5th week of the month and all the days in the week (Sun thru Sat 8/29, 8/30, 8/31, 9/1, 9/2, 9/3, and 9/4) would all be considered as August week 5.

The last formula you gave me does handle this “business rule” perfectly. If you could briefly explain how you did this, I would appreciate it.

Thanks for hanging in there with me.

 

[lbass]

Basically, the formula is transforming the date to the first day of the particular week, so that if the first day is in a particular month, all dates that week will be treated as part of that month.

-LB