Changing existing date format to mmm-yy and sorting by date in a report

[jonathanyeo]

I have data below in YYYY-MM-DD (2020-04-30) format which I want to

1) convert to MMM-YY (Apr-20) and

2) Be able to use the Table reporting tool's 'Group by' function to  earliest to latest date in MMM-YY format

I am able to extract it in MMM-YY by using the parse tool but it's in string format and can't be sorted properly in the Table reporting tool (i.e I will have data sorted alphabetically instead since it's string format)

[Emil_Kos]

Hi @jonathanyeo,

You can always keep the proper date format only for sorting. The data in a format like this can be handy sooner or later so I would suggest keeping the original date column. 

[jonathanyeo]

Thank you Emil, I'd definitely keep the original format, but want to create a new column in MMM-YY format so that my data will be grouped in report format by months and not exact dates.

[atcodedog05 · Accepted solution]

Hi @jonathanyeo ,

Here is a workflow for the task.

Input

Output

All dates are converted to first day of the month so that it can be grouped and later sort and Month.

Take Format column to the output. Date is considered by Alteryx only in a specific format.

Hope this helps : )

If this post helps you please mark it as solution. And give a like if you dont mind : )

[jdunkerley79 · Accepted solution]

I suggest you use a formula tool to create a date column with the date trimmed to start of the month:

DateTimeTrim([Date],"month")

Additionally, produce a second field (Formatted Month):

DateTimeFormat([Month],"%b-%Y")

You can then use both for grouping (with the Month field first) and then format the table as you need.

Quick simple example attached

[jonathanyeo]

Thank you both, never thought of using the trim formula!

[atcodedog05]

I agree with you on the trim function @jonathanyeo 

Happy to help : ) 

Cheers and Happy Analysing 🥳