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'





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

          Try this for MonthYear


          MonthName([Subs_Invoice_Invoice_Date]) as Invoice_Month




          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