Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Determine first monday of each month

BYoung
6 - Meteoroid

Hello, 

I am looking for a way to determine the date of the first Monday of each month based on the date provided.

 

For example...

Date FieldFirst Monday of the Month
2021-06-112021-06-07
2021-05-212021-05-03
2021-05-05

2021-05-03

2021-03-13

2021-03-01

2021-01-09

2021-01-04

 

The fact that monday comes on different days, and months can have 4 or 5 weeks is throwing me off.

 

Any ideas?

 

Thank you all in advance!

10 REPLIES 10
Emil_Kos
17 - Castor
17 - Castor

Hi @BYoung,

 

I have prepared a solution for you.

 

First I created a column showing the first day of the month. Afterwards, I am generating a row for the next 7 days in a weak. I create a column that shows the day of the week. Filter the data to keep only Mondays and as the last step, I am keeping the one row for each of the lines in the initial data.

 

Emil_Kos_0-1623452336136.png

 

apathetichell
18 - Pollux

datetimeadd(datetimeparse(datetimeformat([Field1],"%Y-%m")+"-01","%Y-%m-%d"),mod(8-tonumber(datetimeformat(datetimeadd([Field1],-tonumber(datetimeformat([Field1],"%d"))+1,"days"),"%u")),
7),"days")

 

In formula tool - new date type.

 

the %u determines the day of the week. We know a monday is a day one. 8 -%u is the days until the next monday... but what if the monday is the first day? Well that's why we have to mod by 7 so we are adding 0 days. in that case. took some playing around with.

 

Just noticed that I posted the earlier version I had of this... above is the correct formula. It does the addition and mod versus the first of the month as needed for the rest of the formula.

 

 

suby
11 - Bolide

Hi Emil_Kos,

 

Could you please explain what the formulas does in the Generate rows tool.

 

suby_0-1623490834853.png

 

Emil_Kos
17 - Castor
17 - Castor

Hi @suby,

 

of course. So basically I am creating 7 new rows of data for each original row. New row have a new column with a next day comparing to previous one.

 

The row that I am using as a starting one is always for the first day of the month. So if the first row of data was for the 1st of may 2021 the next one will be for the 2nd of may. Next one will be 3rd of may etc.

 

I am doing that to identify the first Monday for each of the months in the data.

 

I hope this makes sense.

suby
11 - Bolide

Thanks for your explanation.

 

Sorry just to clarify on Your Generate Rows tool. 

 

I understand you are adding 7 new rows of data for each original row on your conditional expression but one thing I'm not getting is why you have [First Monday]<DateTimeAdd([Date Field],+7,'day')

 

secondly could you please explain what the loop condition does?

DateTimeAdd([First Monday],+1,'day')

 

Thanks

apathetichell
18 - Pollux

Suby,

 

@Emil_Kos's excellent workflow uses [first monday] as the new variable for generate rows (it's usually Row Count - but here it's changed to a date variable)... It has to have 7 days after the first of the month as the max number of days generated - this can be derived through some messy datetimeparse/datetimeformat combo or as he does it.

 

The second argument is how to increment the date/time new variable. It doesn't get rowcount +1 - since it's a date, so it gets datetimeadd([first monday],1,"days")

 

And if you really want to explore datetime check out my solution of:

datetimeadd(datetimeparse(datetimeformat([Field1],"%Y-%m")+"-01","%Y-%m-%d"),mod(8-tonumber(datetimeformat(datetimeadd([Field1],-tonumber(datetimeformat([Field1],"%d"))+1,"days"),"%u")),
7),"days")

 

which I posted incorrectly yesterday.

suby
11 - Bolide

Hello apathetichell,

 

Thank you so much for your explanation really much appreciated and will try your solution.

 

Many Thanks

BYoung
6 - Meteoroid

@Emil_Kos This is excellent, works perfectly!

 

Thank you!

BYoung
6 - Meteoroid

@apathetichell , Fantastic! It has taken me a minute to step through it to understand how it's working but it makes sense now. I like being able to do the calculation in one step!

 

Thanks!

Labels