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

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)

Reporting

Common Use Cases

Accepted answers

atcodedog05

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 : )

date.yxmd

jdunkerley79

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

Month Table.yxmd

All comments

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.

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703