Alteryx Designer

Find answers, ask questions, and share expertise about Alteryx Designer.
Andy Uttley, Alteryx ACE, makes music with Alteryx | Math + Music
SOLVED

Date format to Quarter

Highlighted
5 - Atom

I have a question is there an easy way or an add-on to Alteryx that will automatically convert the data format 2017-01-01 into 2017 Q1 ? How can I figure this out without a bunch of weird codes? Thanks.

 

 

Highlighted
13 - Pulsar
13 - Pulsar

No add ons or instant tools for this, that I know of.

 

A couple options, the one I like best (and use myself) is a macro.  Code it once, and you never have to again.

 

Personally, I'd make it one not-terribly-long if/then statement.  I prefer boolean if/then, but you can use traditional if/then/else or if/then/elseif if you like.  

 

Here's my formula:

 

Left([incomingdate],4)+' Q'+

IIF(Substring([incomingdate],5,2) IN ('01','02','03'),'1',
IIF(Substring([incomingdate],5,2) IN ('04','05','06'),'2',
IIF(Substring([incomingdate],5,2) IN ('07','08','09'),'3',
IIF(Substring([incomingdate],5,2) IN ('10','11','12'),'4',
Null()

))))

 

 

You make that into a macro and it just becomes another tool.

 

Highlighted
Alteryx Certified Partner

I would install the tools created by James Dunkerley. I use them so often they may as well be a native part of the Alteryx platform.

 

 

https://community.alteryx.com/t5/Data-Preparation-Blending/Formula-AddIn-to-make-working-with-Dates-...

 

 

 

 

 

 

Highlighted
Alteryx
Alteryx

The following formula should also get you there:

 

DateTimeFormat([Date],'%Y Q') + ToString(Ceil(ToNumber(DateTimeFormat([Date],'%m'))/3))

 

However, that may fall into your category of 'a bunch of weird codes'

Highlighted
5 - Atom

Thanks

Highlighted
5 - Atom

This one works great, thanks!

Highlighted
7 - Meteor

Thank you!  This just worked perfectly for me.

It would normally fall under my category of 'a bunch of weird codes' as well but I was willing to take the chance.

Highlighted
8 - Asteroid

Love the formula however how would you shift the quarters if our Fiscal Year started Oct 2017?

Highlighted
Alteryx
Alteryx

@pvara,

 

You could write a more complicated IF Then formula, however the easiest way would be to shift your Date by 3 months, so wherever [Date] is referenced, replace with DateTimeAdd([Date],3,'months')

Highlighted
5 - Atom

Thank you it works well

Labels