Date format to Quarter

[ntelyuk]

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.

[mbarone]

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.

[Kanderson]

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

[KaneG · Accepted solution]

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'

[ntelyuk]

Thanks

[ntelyuk]

This one works great, thanks!

[mocalvo]

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.

[pvara]

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

[KaneG]

@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')

[anilmekna]

Thank you it works well