Group by weeks, starting 2 days ago

[Ashenka]

hi

i only have access to the reports designer (not sql)

in my report i want to show the last 53 weeks, but a week needs to go from 2 days ago

for example today (19-Dec-2014) i want the date to run from dec something last year (this bit is fine) to 17-Dec-2014, but only as full weeks
for this example it would be Thur 11th to Weds 17th

how can i do this in a variable / grouping?

 

[LaurieHamlin]

First create formula to subtract 2 days from the current date. DateAdd("d", -2, CurrentDate)
How far back do you need to go? From what you've said so far, I'd use DateDiff function to get the number of weeks from a starting point, if you're using a set starting point. If it's not fixed, but always 10 weeks ago, use DateAdd to subtract 10 weeks and then DateDiff to get the weeks. Then group on the formula.
I hope that helps.

 

[Ashenka]

thanks for the reply

what i have so far is....
I have my start date - which is


=RelativeDate(CurrentDate();-373)


i have my full weeks now (which can be thurs to weds, or tues to mon etc)

=If(DayNumberOfWeek(CurrentDate())=1;2;If(DayNumberOfWeek(CurrentDate())=6;4;If(DayNumberOfWeek(CurrentDate())=7;3;If(DayNumberOfWeek(CurrentDate())=1;2;If(DayNumberOfWeek(CurrentDate())=3;7;If(DayNumberOfWeek(CurrentDate())=5;5;If(DayNumberOfWeek(CurrentDate())=4;6;1)))))))


and then:
=Week(RelativeDate([Network].[Departure Date];[AdjustedWeekDay]-1)) (to make an offset week number)



where i am falling over now is:
=FormatNumber([LastYearThisYearWeek1];"0000") + "-" +FormatNumber([WeekNumber];"00")

this is great except for where we go over the new year
so i am trying to do something like:




=If([WeekNumber]=1;
If([SchedDateAdjusted]<[AdjustedWeekDay]+1;
If(MonthNumberOfYear([Departure Date])=1;YearDeparture Date])-1;Year([Departure Date]));
Year([Departure Date]));
Year([Departure Date]))

what i need is this to be 2013_01?

04/01/2014 1 2014-01
03/01/2014 1 2014-01
02/01/2014 1 2014-01
01/01/2014 1 2014-01
31/12/2013 1 2013-01
30/12/2013 1 2013-01
29/12/2013 1 2013-01




any thoughts?