1 Reply Latest reply: Jun 22, 2017 11:12 AM by Marcus Sommer RSS

    Variables with Actual Calendarweek, Last Calendarweek, and Actual Calendarweek-4 wks

    Christian Schmitz



      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?