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

      Hi,

       

      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?