Oracle Analytics Cloud and Server

Home› Oracle Analytics› Oracle Analytics Forums› Oracle Analytics Cloud and Server› Oracle Analytics Cloud and Server

Categories

Welcome to the Oracle Analytics Community: Please complete your User Profile and upload your Profile Picture

Sort Month Year by Calendar Year

Received Response
41
Views
1
Comments
user2182645
user2182645 Rank 1 - Community Starter
edited Aug 13, 2024 5:25PM in Oracle Analytics Cloud and Server

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

Answers

  • Gianni Ceresa
    Gianni Ceresa Mod

    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.