Weeks in a month?

[Drus]

I need a formula that will return a value for which week the transaction date falls within the current month (1-5). In other words, if the transaction date is 2/3/2003 the result should be "2" since it is based on a Sunday - Saturday week. Another example is if the transaction date is 10/8/2003 the result should be "2" as well because it is the second week in October.

Here is what I have which only gives me what week it is in the current year.

DatePart ("ww",{GL5_DetailPosting.TransactionDate})



&ru

 

[dgillz]

If you want to do this for the previous year just slightly modify your formula:

DatePart ("ww",dateadd("y",-1,{GL5_DetailPosting.TransactionDate})

Software Sales, Training, Implementation and Support for Exact Macola, eSynergy, and Crystal Reports

http://www.trianglepartners.com

 

[Drus]

Thanks, but I need it to tell me what week in the current month it is. And the fiscal year is slightly different than a calendar year. For example, the year starts at 12/29/02 and ends at 12/28/03. Even the months are atypical. Period 1 starts 12/29/02 and ends on 2/2/03.

&ru

 

[lbass]

You could try the following:

Datepart("ww",{GL5_DetailPosting.TransactionDate})+1
- Datepart("ww",{GL5_DetailPosting.TransactionDate}
- Day({GL5_DetailPosting.TransactionDate})+1)

-LB

 

[FVTrainer]

LB: Very cool.

Another way to approach this problem, since you have fiscal periods instead of calendar periods is to use a parameter to capture your fiscal begin date (in the example below, {?MonthBeginDate}) and then use the following formula:

Truncate(({GL5_DetailPosting.TransactionDate}
- {?MonthBeginDate})/7) + 1

This approach is more restricted than LB's and assumes 1) the parameter approach is viable for your scenario, and 2) you also limit records in the report to only those transactions in the current month. In other words, if you wanted a report that spans months (let's say you're grouping by month) and you wanted the formula to show the week # for the month in which the transaction occured, this approach by itself is not sufficient.

 

[Drus]

Both answers have minor flaws in them and I'm not sure they can be corrected using either approach. I am thinking at this point it might be easier to have the user enter a parameter that passes through to the report.

Thanks gurus,
&ru

&ru

 

[lbass]

It would be more helpful if you explained what these "minor flaws" were, or why they don't meet your needs. Maybe they can be tweaked! Provide examples of what the suggestion returns and also what the result should be. I tested my suggestion, so am interested in what you think is not working.

Are you counting as week one the week which contains the 1st of the month? And I'm assuming that the week number is based on all weeks that include any date within the month of interest. A date in the month of January would evaluate the week starting 12/29/02 as week 1 and the last week of January, ending 2/1/03 (week 5). If you were looking at a date in February 2003, there would also be 5 weeks: 2/1/03 is in week 1 and 2/23/03 to 2/28/03 are in week 5. Is this not what you want?

-LB

 

[FVTrainer]

Yeah, I think if you listed several dates in the context of, say, two fiscal months and which week they would evaluate as, we'd have a good shot at fixing the "minor flaws".

 

[Drus]

Below is the pattern to follow. I am getting a "5" as a result for 12/29/02 rather than a "1"? And how do I compensate for this?

Sunday Saturday Week #
12/29/2002 1/4/2003 1.00
1/5/2003 1/11/2003 2.00
1/12/2003 1/18/2003 3.00
1/19/2003 1/25/2003 4.00
1/26/2003 2/1/2003 5.00
2/2/2003 2/8/2003 1.00
2/9/2003 2/15/2003 2.00
2/16/2003 2/22/2003 3.00
2/23/2003 3/1/2003 4.00
3/2/2003 3/8/2003 5.00


&ru

 

[FVTrainer]

What is the business rule for determining a week that is split between two months? In the sample data you gave, the week starting 12/29/02 is week 1 of January, but for the week beginning 1/26/03, the week is #5 of January, not #1 of February. So how do you determine if it is week 5 of the first month or week 1 of the next month? And did you really mean for the week beginning 3/2/2003 to be week #5?

 

[lbass]

You said: "I need a formula that will return a value for which week the transaction date falls within the current month (1-5)." Are you now changing the requirements so that the weeknumber does not refer to the "current" month, ie., the month of the date being evaluated?

My solution was based on determining the week of the month that corresponded to the month of the date, so that 12/29/02 would be evaluated for the weeknumber of December, in which case "5" is the correct answer. If the selected date were 1/1/03, the result would be "1" since the date falls in the first week of the month of the date--even though both 12/29/02 and 1/1/03 fall within the same Sunday to Saturday week.

Do you want all dates (whether December or January) within that Sunday to Saturday week to return the result "1"? And are all subsequent periods five weeks in length?

-LB