alteryx Community

# Alteryx Designer Desktop Discussions

SOLVED

## Return First Day of Quarter

5 - Atom

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.

4 REPLIES 4
ACE Emeritus

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```

ACE Emeritus
```Left([Date],5)+
+ "-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!)

13 - Pulsar

Here is my solution. Hope this is helpful.

5 - Atom

Worked like a charm! Thank you!

Labels