Sort issues after converting dates into Q and Months

martin_hamilton — Wed, 31 Aug 2016 21:00:02 GMT

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 — Wed, 31 Aug 2016 21:10:50 GMT

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

topic Sort issues after converting dates into Q and Months in QlikView

Sort issues after converting dates into Q and Months

Re: Sort issues after converting dates into Q and Months