Hello,
Could anyone please help me with a doubt in alteryx? I have a set of data with dates. I need to add the weeknumbers and monthnumbers in a specific format ie, if the date belongs to the current week the weeknumber=0, monthnumber=0, if date belongs to previous week, week number= -1 and month number = 0, if the date belongs to previous month, monthnumber=-1
Please find the sample of data mentioned below.
DatesTable | ||
Dates | Week | Month |
17/09/2019 | -3 | -1 |
18/09/2019 | -3 | -1 |
19/09/2019 | -3 | -1 |
20/09/2019 | -3 | -1 |
21/09/2019 | -3 | -1 |
22/09/2019 | -2 | -1 |
23/09/2019 | -2 | -1 |
24/09/2019 | -2 | -1 |
25/09/2019 | -2 | -1 |
26/09/2019 | -2 | -1 |
27/09/2019 | -2 | -1 |
28/09/2019 | -2 | -1 |
29/09/2019 | -1 | -1 |
30/09/2019 | -1 | -1 |
01/10/2019 | -1 | 0 |
02/10/2019 | -1 | 0 |
03/10/2019 | -1 | 0 |
04/10/2019 | -1 | 0 |
05/10/2019 | -1 | 0 |
06/10/2019 | 0 | 0 |
07/10/2019 | 0 | 0 |
08/10/2019 | 0 | 0 |
09/10/2019 | 0 | 0 |
10/10/2019 | 0 | 0 |
Kindly help me in creating week number and monthnumber for the dates.
Thanks In Advance,
Anagha
Solved! Go to Solution.
Hi @Anagha
You can use the DateTimeFormat to get the week number and month number of your date field after parsing it. Then use the same function for DateTimeToday() and take it from your column.
Hi @Anagha ,
The attached workflow should work with your use case.
What I essentially did was find the week number and month number of the date and today's date. Then subtracted the new numbers together.
Note that the previous week is based on the assumption that Sunday is the last day of the week. So Sunday 06/10/2019 will be treated as the previous week. Please let me know if you would like this to be changed.
Let me know if you have any questions about the workflow.
Thanks,
Josh
Hi Josh,
Thank you so much for the help. This really worked!
Hi Joe,
Thank you so much for the help. This really worked.
@Anagha wrote:
Hi Joe,
Thank you so much for the help. This really worked.
You're welcome. Nice to get two replies within 5 minutes of one another saying the same thing 🙂
@JoshuaGostick - Great minds and that!
Hi Joe :)
Thank you for your solution.
Is there a way of changing the week start day as Sunday?