Group formula for last 13 weeks needed 1

[AZdesertdog]

I'm trying to create a formula that I can use to group by week for the last 13 weeks of a date of service. "Current" would be the first full week starting with monday-
I'm using the formulas:

CurrentDate - DayofWeek(currentdate,crMonday) - 6

CurrentDate - DayofWeek(currentdate,crMonday)

I need the formula that's going to group them by "Current,Week 2, Week 3, Week 4,etc to Week 13"

I've tried an if-then-else with the above formulas but getting an error that one of my calculations is a boolean. Anyone have an idea? Thanks in advance.

 

[AZdesertdog]

Tech info; CR 10 with MSQL db

 

[vidru]

First, you can restrict the records returned to the last 13 weeks by using this in your Record Selection Formula:
[tt]
{Table.DateField} in [DateAdd("ww", -12, CurrentDate - (DayofWeek(CurrentDate, crMonday) - 1)) to CurrentDate]
[/tt]
Then, create this formula, and group on it:

//@Group

DateVar array TheWeeks;

ReDim TheWeeks[13];

NumberVar i;

TheWeeks[1] := CurrentDate - DayofWeek(currentdate,crMonday ) - 1; 

For i := 2 to Ubound(TheWeeks) do(
    TheWeeks[i] := TheWeeks[1] - ((i - 1) * 7); 
    );

Select {Table.DateField} 
   Case  TheWeeks[13] to TheWeeks[12] - 1:
      "Week 13"
   Case  TheWeeks[12] to TheWeeks[11] - 1:
      "Week 12"
   Case  TheWeeks[11] to TheWeeks[10] - 1 :
      "Week 11"
   Case  TheWeeks[10] to TheWeeks[9] - 1 :
      "Week 10"
   Case  TheWeeks[9] to TheWeeks[8] - 1 :
      "Week 09"
   Case  TheWeeks[8] to TheWeeks[7] - 1 :
      "Week 08"
   Case  TheWeeks[7] to TheWeeks[6] - 1 :
      "Week 07"
   Case  TheWeeks[6] to TheWeeks[5] - 1 :
      "Week 06"
   Case  TheWeeks[5] to TheWeeks[4] - 1 :
      "Week 05"
   Case  TheWeeks[4] to TheWeeks[3] - 1 :
      "Week 04"
   Case  TheWeeks[3] to TheWeeks[2] - 1 :
      "Week 03"
   Case  TheWeeks[2] to TheWeeks[1] - 1 :
      "Week 02"
   Case TheWeeks[1] to CurrentDate:
      "Current";

A bit long-winded perhaps, but should do the trick.

-dave

 

[AZdesertdog]

Outstanding! Thanks, Dave. Just what I was looking for.

 

[johnstrang]

Just for peoples' information, I have a similar report which works on the last 13 full weeks. I have formulas
{@start_first_week}

DataDate-DayOfWeek(DataDate) - 82

{@end_last_week}

DataDate-DayOfWeek(DataDate) + 1

then another formula
{@days_old}

{@end_last_week} - date({REPORTER_STATUS.FIRSTOCCURRENCE}

I group by weeks using the group name formula

select {@days_old}
case ( 0 to  6) : totext({@end_last_week} -  6) & " to " & totext({@end_last_week})
case ( 7 to 13) : totext({@end_last_week} - 13) & " to " & totext({@end_last_week} -  7)
case (14 to 20) : totext({@end_last_week} - 20) & " to " & totext({@end_last_week} - 14)
case (21 to 27) : totext({@end_last_week} - 27) & " to " & totext({@end_last_week} - 21)
case (28 to 34) : totext({@end_last_week} - 34) & " to " & totext({@end_last_week} - 28)
case (35 to 41) : totext({@end_last_week} - 41) & " to " & totext({@end_last_week} - 35)
case (42 to 48) : totext({@end_last_week} - 48) & " to " & totext({@end_last_week} - 42)
case (49 to 55) : totext({@end_last_week} - 55) & " to " & totext({@end_last_week} - 49)
case (56 to 62) : totext({@end_last_week} - 62) & " to " & totext({@end_last_week} - 56)
case (63 to 69) : totext({@end_last_week} - 69) & " to " & totext({@end_last_week} - 63)
case (70 to 76) : totext({@end_last_week} - 76) & " to " & totext({@end_last_week} - 70)
case (77 to 83) : totext({@end_last_week} - 83) & " to " & totext({@end_last_week} - 77)

Hope this may help someone else - I'm happy with it

John