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 support@alteryx.com for assistance.

Alteryx Designer Discussions

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

Select closest date to first of month

Dennisklad1
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

8 REPLIES 8
mbarone
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.

Dennisklad1
8 - Asteroid

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

mbarone
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?

pedrodrfaria
13 - Pulsar

Hi @Dennisklad1 

 

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

 

pedrodrfaria_0-1609947833990.png

 

Is this what you are trying to accomplish?

 

Pedro.

 

Dennisklad1
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

pedrodrfaria
13 - Pulsar

@Dennisklad1 

 

Is this what you are looking for?

pedrodrfaria_0-1609950150369.png

 

Ladarthure
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"

echuong1
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!

 

echuong1_0-1609950597397.png

 

Labels