Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

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

Return First Day of Quarter

rohne
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
BenMoss
ACE Emeritus
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

 

jdunkerley79
ACE Emeritus
ACE Emeritus
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!)

ponraj
13 - Pulsar

Here is my solution. Hope this is helpful.

workflow.PNG

rohne
5 - Atom

Worked like a charm! Thank you!

Labels