3 Replies Latest reply: Dec 20, 2017 9:57 AM by Chris Hopkins RSS

    Help finding week number based on counter field

    Chris Hopkins

      Hi all,

       

      i have a date variable and a calendar which includes a week_counter field. what i am trying to do is find the date variable in the calendar and retrieve the associated week number - store this to a variable. then i want to go back 52 weeks from the week number found in the previous step but by using the week_counter field.

       

      eg.

       

      DateWeekWeek_counter
      01/01/20171100
      08/01/20172101
      15/01/20173102
      22/01/20174103
      29/01/20175104
      05/01/20176105
      12/01/20177106

       

      so in the above, if my variable date was 12/02/2017 and i want to go back 4 weeks, then i want the following...

      1.     store week no '7' to a variable (which is the corresponding week for the variable date of 12/01/2017)

      2.     find the corresponding Week_counter for week 7 (which is 106), then go back 4 (which is 102) and store the corresponding week         (which is '3') to another variable. 

       

      i want to use the counter field because this takes any leap yrs and other customer variables into account in my customers data.

       

      i am happy with the variable option as stated above, but if its easier then a flag in the data would be beneficial instead, so a new field called YTD which is flagged as 1 if the week numbers are in the current range (so in the above example, there would be a 1 against weeks 3-7)

       

      can anyone help with this please?

       

      i have attached a sample.

       

      thanks in advance