Return First Day of Quarter

rohne

Hi Alteryx Users,

I have a column of dates in form:

2017-02-01

2017-05-01

2017-11-01

I'm trying to return the first date of the quarter the date is in, such as:

2017-01-01

2017-04-01

2017-10-01

I used DateTimeTrim([Field1],"month") to get the first set of dates, is there a way to do the same for quarter? DateTimeTrim([Field1],"quarter") doesn't seem to work. I need the end output to be in date form, not a string/integer.

Tips and Tricks

Custom Tools

Date Time

Best Practices

Help

Accepted answers

jdunkerley79

Left([Date],5)+
PadLeft(ToString(3*Floor((DateTimeMonth([Date])-1)/3)+1,0),2,"0")
+ "-01"

should work for all.

Floor((DateTimeMonth([Date])-1)/3)

Computes the Quarter (0,1,2,3)

it then is converted to a month by times by 3 and adding 1 (1,4,7,10)

Finally converts back to a date

Sample attached.

(Shamelss plug for my Alteryx Abacus functions which adds a QuarterStart function to Alteryx making this trivial!)

Quarter start.yxmd

All comments

BenMoss

Here's a two step process that although is not ideal, will definitely work and is obviously never going to fail!

Use a formula to create a field which is the month value for the date as an integer, lets call this field [MONTH].

tonumber(Substring([Field1],5,2))

Then from this we can generate our quarter date, again using a formula tool. This can be as a date type field too.

IF [Month] <= 3 THEN LEFT([DATEFIELD],4)+"-01-01"
ELSEIF [Month] <= 6 THEN LEFT([DATEFIELD],4)+"-04-01"
ELSEIF [Month] <= 9 THEN LEFT([DATEFIELD],4)+"-07-01"
ELSE "LEFT([DATEFIELD],4)+"-10-01"
ENDIF

jdunkerley79

Left([Date],5)+
PadLeft(ToString(3*Floor((DateTimeMonth([Date])-1)/3)+1,0),2,"0")
+ "-01"

should work for all.

Floor((DateTimeMonth([Date])-1)/3)

Computes the Quarter (0,1,2,3)

it then is converted to a month by times by 3 and adding 1 (1,4,7,10)

Finally converts back to a date

Sample attached.

(Shamelss plug for my Alteryx Abacus functions which adds a QuarterStart function to Alteryx making this trivial!)

Quarter start.yxmd

ponraj

Here is my solution. Hope this is helpful.

Return First Day of Quarter.yxmd

rohne

Worked like a charm! Thank you!

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703