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)