HELP formula with alternate start of week day 1

[Janny]

Hi
I am being asked to pull data based on a week that begins on a wednesday and ends on a tuesday. Ocassionally the first day of week 1 is actually a wednesday of the prior month. I am a newbie and very green and dont know how to do this. I am using version 9.

thanks in advance for your help

 

[lbass]

Please provide more information about what you are trying to do and the report structure. If you were grouping data based on the week of the year, you could use:

datepart("ww",{table.date},crWednesday)

-LB

 

[Janny]

Ok this is what I am doing. I have a group of program managers who have projects with due dates stored in a foxpro database. My report tells how many projects were on time or late and how many are scheduled for the future. Here is the formula I have been using but now they want the weeks to start on a wednesday and end on a tuesday.
thannks



tringVar month_yr := ToText(Month({oracle.sfdate})) + ToText(Year({oracle.sfdate}));
stringVar current_month_yr := ToText(Month(CurrentDate)) + ToText(Year(CurrentDate));
numberVar day_num := Day({oracle.sfdate});

stringVar actf_month_yr := ToText(Month({oracle.actf})) + ToText(Year({oracle.actf}));
numberVar actf_day_num := Day({oracle.actf});

dateVar range N30Days := CurrentDate to Maximum(Next30Days);

if {@Releases} = 'On Time' or {@Releases} = 'Late' then
if {oracle.actf} in LastFullMonth then
MonthName (Month({oracle.actf}))
else
if actf_day_num >= 1 and actf_day_num <= 7 and actf_month_yr = current_month_yr then
'Wk 1'
else
if actf_day_num >= 8 and actf_day_num <= 14 and actf_month_yr = current_month_yr then
'Wk 2'
else
if actf_day_num >= 15 and actf_day_num <= 21 and actf_month_yr = current_month_yr then
'Wk 3'
else
if actf_day_num >= 22 and actf_day_num <= 28 and actf_month_yr = current_month_yr then
'Wk 4'
else
if actf_day_num >= 29 and actf_day_num <= 31 and actf_month_yr = current_month_yr then
'Wk 5'
else
if {oracle.actf} < Minimum(LastFullMonth) then
'Before'
else
'After'
else
if {@Releases} = 'On Timex' then
if {oracle.sfdate} in LastFullMonth then
MonthName (Month({oracle.sfdate}))
else
if day_num >= 1 and day_num <= 7 and month_yr = current_month_yr then
'Wk 1'
else
if day_num >= 8 and day_num <= 14 and month_yr = current_month_yr then
'Wk 2'
else
if day_num >= 15 and day_num <= 21 and month_yr = current_month_yr then
'Wk 3'
else
if day_num >= 22 and day_num <= 28 and month_yr = current_month_yr then
'Wk 4'
else
if day_num >= 29 and day_num <= 31 and month_yr = current_month_yr then
'Wk 5'
else
if {oracle.sfdate} < Minimum(LastFullMonth) then
'Before'
else
'After'
else
select {oracle.sfdate}
case N30Days :
'0-30 Days'
case Next31To60Days :
'31-60 Days'
case is > DateAdd('d',60,CurrentDatetime) :
'61+ Days'
case Aged0To30Days :
'A1-30 Days'
case Aged31To60Days :
'A31-60 Days'
default:
'A61+ Days'

 

[lbass]

Try the following simplication of your formula:

if {@releases} in ['OnTime','Late'] then
if {oracle.actf} in LastFullMonth then
MonthName (Month({oracle.actf})) else
if date(year({oracle.actf}),month({oracle.actf}),01) =
date(year(currentdate),month(currentdate),01) then
"Wk"+totext(datepart("ww",{oracle.actf},crWednesday)+1-
datepart("ww",{oracle.actf}-day({oracle.actf})+1,crWednesday),0) else
if {oracle.actf} < Minimum(LastFullMonth) then
'Before' else
'After' else

if {@releases} = 'OnTimex' then
if {oracle.sfdate} in LastFullMonth then
MonthName (Month({oracle.sfdate})) else
if date(year({oracle.sfdate}),month({oracle.sfdate}),01) =
date(year(currentdate),month(currentdate),01) then
"Wk"+totext(datepart("ww",{oracle.sfdate},crWednesday)+1-
datepart("ww",{oracle.sfdate}-day({oracle.sfdate})+1,crWednesday),0)
else

if {oracle.sfdate} < Minimum(LastFullMonth) then
'Before' else
'After' else

select {oracle.sfdate}
case Next30Days :
'0-30 Days'
case Next31To60Days :
'31-60 Days'
case is > DateAdd('d',60,CurrentDatetime) :
'61+ Days'
case Aged0To30Days :
'A1-30 Days'
case Aged31To60Days :
'A31-60 Days'
default:
'A61+ Days';

You will have to decide how you want the week numbers to work. In this example, if Wednesday is the 4th of the month, then days 1 - 3 will be week 1, days 4 - 10 will be week 2, etc.

-LB

 

[Janny]

That works great! Thank you! If the 4th is on a wed. like you say, is there a way to make the last wed of the previous month week 1 or if the end of the month is on a monday, is there a way to include the first day of the next month in that week?

Thanks!

 

[lbass]

I think you can convert each of your dates, as in the following:

//{@oracleactf}:
{oracle.actf}-dayofweek({oracle.actf},crWednesday)+7

//{@oracle.sfdate}):
{oracle.sfdate}-dayofweek({oracle.sfdate},crWednesday)+7

This will convert each date into the last day of the week (Tuesday) in which the date falls, so that dates in the previous month that are in a week that ends in the new month, will be associated with the new month. Similarly, daets in the month which fall into a week that ends in the next month, will be associated with the next month.

Then substitute these in the earlier formula instead of using the date fields.

The only potential problem, I think, is that I'm not sure how you want the select case part of the formula to work. You might want to leave the date field in that portion of the formula, since the above conversion formulas would always take on the value of the last day of the week, thus distorting your results.

-LB