I am loading data with a where condition.
I just want to load data which is not more old than Last Calendarweek for example.
I have defined the ActualCalendarWeek (works fine):
LET vL.ActualCalendarWeek= left(WeekName(today()),4) & right(Weekname(today()),2);
The LastCalendarWeek is more of a challenge, due to the split between this year and last year, when we stay at week 01, it of course have to be wk1 -1= wk52.
LET vL.LastCalendarWeek= if(match(right($(vL.ActualCalendarWeek),2),25),(left($(vL.ActualCalendarWeek),4)-1)&'52',$(vL.ActualCalendarWeek)-1)
Here the question is, how can I handle the leap year, which instead of wk52 should be wk53! (Next one is in 2020). ???
The NextCalendarWeek I managed to do:
LET vL.NextCalendarWeek= if(match(right($(vL.ActualCalendarWeek),2),52,53),(left($(vL.ActualCalendarWeek),4)+1)&'01',$(vL.ActualCalendarWeek)+1);
BUT as I stated in my caption, I also would like to go back -4 weeks (or + 4 weeks).
Here if we are in week 4 the result should be 52, week 3: 51, week 2: 50, week 1: 49 .... and this is not considering the leap year as well.
Does anybody have a solution for this?
Isn't it nearly the same like here: Problem with a variable: Script Line Error? Instead of -1 for the last week you could use -29 to get the fourth week before and a quite similar logic with weekend() for the future weeks.