I have a table that is updated manually every week using excel based on another excel file sent to me. I would like to automate this process using Alteryx. I want to update report week number(This number indicates how many times we have reported on that month so far for a given quarter) based on financial week and month. Obviously we are now in September but I will show you the first week to give you an idea of how its updated. The first week for 2021 would start on 01/04/2021 (First Monday of the year) and end on 12/27/2021 (Last Monday of the year).
This Alteryx is to be run weekly so next time it is run 01/04/201 --> 01/11/2021, 1 week is added & the "Report Week" should update by 1 too, unless report week is greater than 13. If "Report Week" is greater than 13 than we stop updating that month and add the next month. So in this case we drop December and start reporting March and its report Week becomes 1, as this is the first month we are reporting on it.
| Month | Finance Week | Report Week |
|-------|--------------|-------------|
| December | 01/04/2021 | 13|
| January | 01/04/2021 | 8|
| February | 01/04/2021 | 4|
| January | 01/11/2021 | 9|
| February | 01/11/2021 | 5|
| March | 01/11/2021 | 1|
So Next weeks report will be Finance week 9/27/2021 (I can get this from the file that is sent to me) but I will not have any logic on how to automatically update the report week. I was thinking creating a separate table that contains all the possible | Month | Finance Week | Report Week | values then join that with what I have to get Report Week for the week I am in.
