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.
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'
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.
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-easier-and-a-few-other/m-p/21598#U21598
Thanks
Worked well. ThankQ
My date is a vw-string type in the format year & month:
I need to convert to quarters & years:
etc.
Is there a formula I can use ?
Take a look at the below solution. I used this and modified it to meet your needs. Not sure if that helps because Your output would still not read as a date if you were doing time series analysis.
https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Date-format-to-Quarter/td-p/57291
Thanks @KaneGNifty formula!
I made this to get the end of the previous quarter.
Feels a little long winded, but works nicely.
Pro-tips appreciated 🙂
DateTimeTrim(toDate(DateTimeFormat([date],'%Y') + "-" +padleft(tostring(Ceil(ToNumber(DateTimeFormat(DateTimeAdd([date],-3,"month"),'%m'))/3)*3,0),2,"0") + "-01"), "lastofmonth")
Wrong post, sorry!!
Hello Sir, Source that you referring is deleted, can you share the "tools created by James Dunkerley" please
Thank you!
