Alteryx Designer Desktop Discussions
Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
I want to automate a process that's currently done manually in excel every week. I need to build a table that tells me what financial week it is and how many times the month has been reported on, indicated by the Report Week. The first Financial Week of the year is the first Monday of the year, which in the case of 2021 was 01/04/2021. I have the table up until this week (09/20/2021) but since I want to automate this going forward (for years into the future), I figured I re-start and build from the beginning since it would b easier to explain.
This is how the table looks on the first week of the year. When it is run again the next week Report Week increments by 1, unless it is already 13, if that is the case then that month stops updating and a new month is added in. So here December will stop updating and Jan & Feb Report Week will update to 9 and 5 respectively and March will be added in and its report week will be 1, since its the first time its being reported on. Financial week will also increment by 1 week (7 days) so the new financial week for all the months still being reported on will be 01/11/2021. Only 3 months are reported on at a time.
| Month | Store | Financial Week | Report Week |
| December | A | 01/04/2021 | 13 |
| December | B | 01/04/2021 | 13 |
| December | C | 01/04/2021 | 13 |
| January | A | 01/04/2021 | 8 |
| January | B | 01/04/2021 | 8 |
| January | C | 01/04/2021 | 8 |
| February | A | 01/04/2021 | 4 |
| February | B | 01/04/2021 | 4 |
| February | C | 01/04/2021 | 4 |
Table after Week 2
| Month | Store | Financial Week | Report Week |
| December | A | 01/04/2021 | 13 |
| December | B | 01/04/2021 | 13 |
| December | C | 01/04/2021 | 13 |
| January | A | 01/04/2021 | 8 |
| January | B | 01/04/2021 | 8 |
| January | C | 01/04/2021 | 8 |
| February | A | 01/04/2021 | 4 |
| February | B | 01/04/2021 | 4 |
| February | C | 01/04/2021 | 4 |
| January | A | 01/11/2021 | 9 |
| January | B | 01/11/2021 | 9 |
| January | C | 01/11/2021 | 9 |
| February | A | 01/11/2021 | 5 |
| February | B | 01/11/2021 | 5 |
| February | C | 01/11/2021 | 5 |
| March | A | 01/11/2021 | 1 |
| March | B | 01/11/2021 | 1 |
| March | C | 01/11/2021 | 1 |
Currently I have a generate rows which increments Report Week until Report week hits 13. Then I use a multi-field formula tool to increment the financial week if (Row -1:Month = Month). But I am unsure how to proceed with adding the new month when a month hits 13 weeks and stop updating old months that hit the 13 week limit. I'd also like this to work indefinitely so that at the end of 2021 it can keep going and do 2022,2023.
Hi @Aviator0807
This was a fun one. Try out this technique
The Financial Weeks input is a simple list of the upcoming weeks. After appending this to the original table, I use the following 4 formulas in a formula tool to adjust the displayed month and Report Weeks if the new report week is >13 and then adjust the Report week and calculates the MonthStart used in the final sort.
The final output looks like this
If you scroll down you'll see that at week 2021-02-08 January drops off and April is added.
Dan
