1 Reply Latest reply: Aug 31, 2016 5:10 PM by Sunny Talwar RSS

    Sort issues after converting dates into Q and Months

    Martin Hamilton

      I am converting data on a script and creating new columns but when i place them into a graph i am struggling to sort.

       

      The script is undertaking the following:

       

      'Q' & Ceil(Month([Subs_Invoice_Invoice_Date])/3) & '-' & Year([Subs_Invoice_Invoice_Date]) as Invoice_Quarter,

      Date([Subs_Invoice_Invoice_Date],'MMM') & '-' & Year([Subs_Invoice_Invoice_Date])as Invoice_Month

       

      I cant figure out what sort to apply to have dates going from Dec-2015 through to Jul-2016.

       

      They are all mixed up!

       

      I wasnt sure whether I could apply a sort in the script that would combat this and load by 'Load Order'

       

      Thanks

       

      Martin

        • Re: Sort issues after converting dates into Q and Months
          Sunny Talwar

          Try this for MonthYear

           

          MonthName([Subs_Invoice_Invoice_Date]) as Invoice_Month

           

          or

           

          Date(MonthStart([Subs_Invoice_Invoice_Date])) as Invoice_Month

           

          For QuaterYear, try this:

           

          Dual('Q' & Ceil(Month([Subs_Invoice_Invoice_Date])/3) & '-' & Year([Subs_Invoice_Invoice_Date]), QuarterStart([Subs_Invoice_Invoice_Date])) as Invoice_Quarter,

           

          Now you should be able to use a regular numeric sort for both the fields