Shift Day of Week Start and Week Number

[jamesonian]

Hi All,

I need to do two things with a list of dates: set the week to begin on Wednesday then report a week number in full year plus week number format, similar to "%Y%U" in DateTimeFormat that aligns with the shifted week start. I also need the week number to be +1 since Alteryx starts week numbers on 0 and I need them to start on 1.

In the example below, the left is what Alteryx would do with the dates if I used formulas to get the day of the week and the week number in "%Y%U" format, and the right side is what I actually need. Any thoughts on how to get the right side?

Date	Day of Week	Year and week number		Date	Day of Week	Year and week number
12/27/2020	0	202052		12/30/2020	0	202053
12/28/2020	1	202052		12/31/2020	1	202053
12/29/2020	2	202052		1/1/2021	2	202053
12/30/2020	3	202052		1/2/2021	3	202053
12/31/2020	4	202052		1/3/2021	4	202053
1/1/2021	5	202052		1/4/2021	5	202053
1/2/2021	6	202052		1/5/2021	6	202053
1/3/2021	0	202100		1/6/2021	0	202101
1/4/2021	1	202100		1/7/2021	1	202101
1/5/2021	2	202100		1/8/2021	2	202101
1/6/2021	3	202100		1/9/2021	3	202101
1/7/2021	4	202100		1/10/2021	4	202101
1/8/2021	5	202100		1/11/2021	5	202101
1/9/2021	6	202100		1/12/2021	6	202101

[markcurry]

Hi @jamesonian 

The following formula will gives you Weekdays 0-6 for Wednesday to Tuesday.

Mod(ToNumber(DateTimeFormat([Date],"%u")) + 4,7)

Your Year and week number value is a bit trickier unfortunately.

[markcurry · Accepted solution]

Hi @jamesonian 

See the attached workflow, it's not the most elegant solution but gives you what you want.  It creates the Year and Week values for a date range, and then you can add that to your workflow.  Hope that helps.

Mark

[Qiu]

@jamesonian 

This post should help.

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Week-Number-Calculation-Starting-At-Sp...

[jamesonian]

@markcurry That works! I had the days of the week set to start on Wednesday no problem but was tripping up on getting the week as I needed them. This solves that piece too. Thank you!

[jamesonian]

Hi @markcurry ...thanks again for your previous solution. Now I have another question about it! 

How would you modify the flow you created so that:

1. The week starts on Friday (I figured out this part)

2. The first week of the year shifts forward one week from where your solution puts it (I have no clue)

For example, if I change the multi-row formula in your solution to have the weeks start on Friday I get this for 2021: 

What I want is for week 1 to start on Friday, January 8th instead of the 1st and the previous week starting Jan 1st should be week 53 of the previous year.

I know this is weird, but the use case is calculating promotional weeks for a retailer where a couple of their divisions use different promo weeks than the rest of the company. The rest of the company is using the logic in your previous solution.

Any ideas? Thanks! 

[markcurry]

Hi @jamesonian  Could you simply create the week starting on the Friday, so 1st January - 7th January is Week 1, then add a formula, if week = 1 set it to week 53 (and the year to 2020) otherwise subtract 1 from the week.  It will work for 2021.  So hopefully that does the trick for you.