Adding an identifier based on relative record position

Hey all,

I have encountered a speed bump in creating a repeatable workflow and need some help.

The goal is to be able to run this on a weekly basis, and for the workflow to update based on the user's input of a Fiscal Year and Fiscal Week. This fiscal week will be the target week, but I need to be able to identify the 52 prior weeks as "This year", the 52 before that as "Last year", and so on and so forth. There will be other inputs and analysis that will be joined with this information, but that will be the next step after I find a solution to this problem.

I've created a workflow that identifies the fiscal week that is my "Target", but I need to figure out how to append an identifier to records based on the target week that the user input.

I'm attaching a sample workflow of what I have so far. I hit a roadblock at the sort tool. I have a "Target" field that is null other than for my target week. My plan was to use this fact in the Multi Row Formula tool to identify the 52 records above at TY, etc, but I've realized this method will not work as it's set up.

Has anyone encountered a similar problem and found a solution? Any and all advice would be greatly appreciated!

Best,

Wes

Sample Workflow.yxmd

Preparation

Accepted answers

Kenda

I added on to the workflow you provided after your Union tool. My approach was to get rid of the years after the user's selected date (because, as I understand, you only want the prior years). After that, I append a field to each row that just has the user's selected week value. Then I use the Multi-Row Formula to add a number each time the user's week equals that row's fiscal week (indicating a new year). 1 will correspond to this year, 2 is prior year, etc. Hopefully this helps!