1 Reply Latest reply: Aug 1, 2011 5:58 PM by John Witherspoon RSS

    Date sort order

      Hi All,

       

      I am trying to sort the date field but it keeps giving me the wrong order. i want to display Jan through Dec. I tried putting sort by month in the expressions column but that is not working.

       

      how can i do this?

       

      Please refer to sheet 3 of the app.

       

      Thanks!

        • Date sort order
          John Witherspoon

          Ugh.  I didn't notice that the file was 17 MB.  It locked my computer up for a few minutes while it loaded and rendered, and once it was downloaded, the chart you're having problems with got an "out of object memory" error, and my computer was moving at a crawl.  Please don't use examples that large.  Limit the data or create a sample application that demonstrates the problem.

           

          Your calculated dimension is a text field.  You attempt to sort by MonthStart, but you have no such field.  It is probably sorting in text order.  I can't see it to know for sure.

           

          I would create a date(floor(START_TIME)) as START_DATE field in the REP_TASK_INST_RUN table.  I would then create a Calendar table, and add a Month field to the calendar table, defined as date(monthstart(START_DATE),'MMM YYYY') as START_MONTH.  Well, I wouldn't use all caps, and I'd put spaces in my field names, but that's the idea.  If you don't want to tackle a calendar table, put the START_MONTH field in the REP_TASK_INST_RUN table.  Same idea.  Once you have your month field defined as a date, it will sort like a date, which is to say in the correct order.