Alteryx Designer Desktop Discussions

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

Date Format/Conversion

srplesz
5 - Atom

I'm currently trying to calculate trailing 12 month financials and our financial data is coming through as a string in the following format "mmYYYY"
Therefore it doesn't sort properly. 
I'm having difficulties getting the date to convert to an actual date . 

 

Thanks!

2 REPLIES 2
Hub119
11 - Bolide
11 - Bolide

I would suggest the following steps... 1) Convert your MonthYear field to string if isn't already stored that way, 2) use the following function to get your data in the right starting format to correct for single digit months: "padleft([MonthYear],6,'0')" and finally 3) The function "DateTimeParse([MonthYear],'%m%Y')" will return you a properly formatted date data for the first of the given month which should allow you to properly sort.

binuacs
21 - Polaris

@srplesz 

image.png

IIF(length(toString([MonthYear]))<6,DateTimeParse('01'+PadLeft(toString([MonthYear]),6,'0'),'%d%m%Y'),DateTimeParse('01'+toString([MonthYear]),'%d%m%Y'))
Labels
Top Solution Authors