Has anyone used Alteryx to develop a Retail 4-5-4 Calendar.
Input (Text File)
Year: 2021
Start Date: 2021-01-31
End Date: 2022-01-29
I wanted to be able to use those 3 input data points to develop a 4-5-4 calendar for use within a workflow. I will need the following information:
Year
Period
Week in Year
Week in Month
See attached for a downloaded excel version in calendar view. I prefer to not download every year. I want to build on the fly.
I am pretty sure that the follow tools will be used - Generate Rows and multi-field formulas.
Thanks in advance.
Résolu ! Accéder à la solution.
Hi @MatthewBr -
You are on the right track! You can use a combination of Generate Rows and Multi-Row Formulas to accomplish this. I have attached an example here.
Hi @MatthewBr ,
You are correct sir!
I've built this to use the tile tool to determine the period on a 4 way equal split, but you can remove this and just have a text input tool (or an app) to input the period start dates and join accordingly, then simply fill in the blanks.
Hope this helps,
M.
@mceleavey - Close but not 100% = For example. The first row - is 2021-01-31 in theory yes it's part of January but in the retail calendar this is the first day of February. Plus the week in year I noted that its week 1 is 2021-01-31 thru 2021-02-06 and week 2 2021-02-07 thru 2021-02-13.,
Period 1 - Dates 2021-01-31 thru 2021-02-27
Period 2 - Dates 2021-02-28 thru 2021-04-03
Period 3 - Dates 2021-04-04 to 2021-05-01
Period 4 2021-05-02 to 2021-05-29
Period 5 2021-05-30 to 2021-07-03
Period 6 2021-07-04 to 2021-07-31
Period 7 2021-08-01 to 2021-08-28
Period 8 2021-08-29 to 2021-10-02
Period 9 2021-10-03 to 2021-10-30
Period 10 2021-10-31 to 2021-11-27
Period 11 2021-11-28 thru 2022-01-01
Period 12 2022-01-02 thru 2022-01-29
Hi @MatthewBr ,
Generate Rows and Multi-Row Formula will work for you.
First step is to generate all dates using the Generate Rows tool. You can use a Formula tool to find the week in year. The Multi-Row Formula is needed to find the month and the week in month. Used the number of days to create the 4-5-4 split.
Let me know if it works for you.
Best,
Roland
@MatthewBr . sorry the tiles were actually the quarters, that's what I was talking about 🙂
I've attached the amended calendar now:
This should now include the correct off-set.
Hope this helps,
M.