Now that it's live, don't forget to accept your certification badge on Credly today! Learn more here.
We are currently experiencing an issue with Email verification at this time and working towards a solution. Should you encounter this issue, please click on the "Send Verification Button" a second time and the request should go through. If the issue still persists for you, please email for assistance.

Alteryx Designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.

Select closest date to first of month

8 - Asteroid

Hi and a happy new Year to all.


I have a record of files with created date metadate with the directory tool. Every month we have multiple files but I would like to select only the one closest to the first of the month.


Dates          Selected

01.03             true

05.02             false

30.01             true

22.01             false

05.01             false

02.01             true

15 - Aurora
15 - Aurora

"Closest to the first of the month".  Which "first of the month"?  Meaning, relative to what month?  First that question has to be answered.  Once you have that answer, you can use the DateTimeTrim([month],'firstofmonth') function, and then use a DateTimeDiff function to calc the number of days between that first of the month and your value, and then take the minimum but using a summarize tool and join.


But first we'd need to know which "month" you want to get to the first of.

8 - Asteroid

Ow sorry for the confusion. First of the month I mean the 01.** (**.01 for mm.dd format) of each month

15 - Aurora
15 - Aurora

So if you have the following incoming dates:


1.  Dec 30 2020

2.  Jan 03 2021

3.  Dec 15 2020

4.  Nov 16 2020


Which first of the month do want to reference?

13 - Pulsar

Hi @Dennisklad1 


I have attached below a workflow that considers the closest date to the first of the month per each month.




Is this what you are trying to accomplish?




8 - Asteroid

I'd like for every single month only one record that is the closest to the start of the month.


If I have:


01 Dec

03 Dec

25 Dec


then I would select 01 Dec and the same for all other months

13 - Pulsar



Is this what you are looking for?



13 - Pulsar
13 - Pulsar

If you want to calculate this, you can first use a DateTimeFormat([DATE],'%Y%m') which will give you a format like 202101 for this month, then sort yoiur data by this new field first and then by the dates itself.

And finally use a Sample tool to get the first record for each "period"

Alteryx Alumni (Retired)

I am a bit confused by your original example, but I believe I understand what you're looking for.

I started off by converting your values to actual date formats. This automatically made the dates in the year 1400, but the year doesn't matter that much in your case. I then derived the month from the date using a formula. I used a sort to sort by the month and date, so each group of months was together, and the dates were going in ascending order. This allowed me to use a sample tool to grab the first value per month, effectively being the date closest to the beginning of the month.


Hope this helps!