Sort Month Year by Calendar Year

Question

Hello,

i have a report that goes over multiple years.   Currently, I have this formula, to get the date in the format of Year - Month

cast (YEAR("CCD Cases"."Date Signed") as char ) ||  ' - '  || cast(MONTHName("CCD Cases"."Date Signed") as char )

when i bring into a pivot table it will sort them by Year and then Alphabetically on the Month.  I need to sort them on Year then begin with January, Feb, March, etc..  instead of Year April,  Year August,  Year Dec, etc..

Anyone have this issue?  trying to quickly get a report done.

Thanks

Gianni Ceresa · Answer

Use MONTH instead of MONTHNAME and add '0' padding in front for months before October.

What happen is not an issue, it's simple logic: you have a string and if you sort it A come before J, the fact that A is for April and J for January doesn't matter. A string is a string.

You need to either keep the sort column in a date format or you generate a string with the proper format to match the sorting you expect.

That's why columns in the RPD have an attribute to set a sorting column: so even texts representing something else (dates in your case) can be sorted properly.