Changing "Month" headers from alphabetical to chronological order

[AlexSimmons]

Hi all,

I have taken a large set of data and extracted out the key information I require, I have transposed a MMM-YY value into just a month and then used cross tab to change these into headers for each month (see below example). The output of the headers are in alphabetical order whereas I want them to show in chronological month order i.e. Jan, Feb, Mar.

I've searched for a solution but nothing seems to cover this, apologies if I'm being stupid, I'm just starting to get to grips with Alteryx.

[alexnajm]

You will need to add in the numeric month beforehand using a Formula of sorts, then after you Crosstab you can remove the numbers with a Dynamic Rename!

[binuacs]

@AlexSimmons similar to @alexnajm suggestion, but here i converted the given MMM-YY to days and converted it back to MMM-YY in the heading using dynamic rename tool

[AlexSimmons]

Thanks both for the responses, @binuacs when I opened your database it had a parse error, from what I can establish because there is a mixture of string and a date value? (could be wrong) but still provides the correct output, however when you use ToDate it classes the output as an integer rather than text (string) so it returns a null value. This subsequently causes the DynamicRename to fail for the same reason, I've run a similar logic with my own data and get the same outcome. 

Appreciate your thoughts and guidance, thanks.

Alex.

[binuacs]

@AlexSimmons can you provide some sample data similar to your input which you were facing parse error?

[apathetichell]

Dynamic Rename-> select all fields:

if !isnull(datetimeparse([_CurrentField_],'%B')) then trimleft(datetimeformat(datetimeparse([_CurrentField_],'%B'),'%m'),'0') else [_CurrentField_] endif

this asusmes that your months are in full month name (August/September) --- if they are like "Aug","Sep" --- use %b

and my assumption is that February is February the following year -> so you want it after December. I'm not resorting your column names here. because I do not want to make an assumption about your compare logic.

[AlexSimmons]

I've amended my data so dynamic dates are now all first of the month i.e. 2024-01-01, 2024-02-01 etc., once the data is cross tabbed it changes it to 2024_01_01, 2024_02_01 etc. which I have learnt is standard in Alteryx.

I have managed to get the desired date columns in "mmm-yy" or "Jan-24" as an example by using two dynamic renames:

DatetimeParse([_CurrentField_],'%Y_%m_%d') to change to ISO format and then DatetimeFormat([_CurrentField_],'%b-%Y') to get to the output as above.

I would have thought that something like DatetimeParse(DateTimeFormat([Name],'%b-%Y'),'%Y_%m_%d') would have been sufficient in a single dynamic rename, however it returns a null value.

Thanks,

Alex.

[apathetichell]

Hey->

1) remember to mark a solution as correct. you can mark multiple solutions.

DatetimeParse(DateTimeFormat([Name],'%b-%Y'),'%Y_%m_%d') doesn't make sense... maybe:

datetimeformat(datetimeparse([_CurrentField_],'%Y_%m_%d'),'%b-%Y')

you are looking to end up with a string (so you need datetimeformat) you need that as your outer function. you convert [_CurrentField_] - which in Dynamic Rename IS the Name. In Multi-Field Formula [_CurrentField_] is the value. and in Dynamic Select [Name] is the name.