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.






      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