Inspire EMEA 2022 On-Demand is live! Watch now, and be sure to save the date for Inspire 2023 in Las Vegas next May.

Alteryx Designer Discussions

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

Dynamic DateTimeFirstOfMonth

crtakacs
8 - Asteroid

I couldn't seem to find an answer to this but is there a way to the DateTimeFirstOfMonth function to be dynamic?

 

I'm trying to create a filter formula that says:

 

IF [BEGTIME]<DateTimeFirstOfMonth() THEN DateTimeFirstOfMonth([X]) ELSE [BEGTIME] ENDIF

 

where I want X to be a date pulled from the data. 

 

In other words, if condition is true then change BegTime column to first of month from another time column. 

2 REPLIES 2
AndrewBanh
9 - Comet

Hi @crtakacs 

 

Just a bit curious about your formula. If you have data that is say on the 22 May 2019 and it is currently 03 June 2019, do you want the formula to return 01 June 2019?

 

Would you want it to instead show the 01 May 2019 if the date in your data is 22 May 2019?

 

I have provided a workflow for each alternative for you. 

 

 

Going through the first formula, the DateTimeFirstOfMonth() will return the first date of the current month (June). The 2nd expression is the same formula you have included below.

First of Month 1.png

As you can see in the browse tool, it has changed the BegTime for every day before June to be 2019-06-01.

 

However, with the 2nd formula tool, the expression finds the first day of the month for every month. This is done by formatting the date in your data source to grab only the year and month and adding the 01 to the end. That means that if the date in your data source is in May, it will return the first of May. The 2 formulas have different outcomes depending on what you want. 

 

First of Month 2.png

 

Please let me know if you have further questions :)

 

- Andrew

danilang
18 - Pollux
18 - Pollux

Hi @crtakacs 

 

As well as @AndrewBanh's excellent solution, you can also use the DateTimeTrim(dt,t) function.  dt is the date you're processing and t is the string "firstofmonth" which will trim your date trimmed to the first of that month.  See the functions page for a complete description.

 

Dan

Labels