Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
martin_hamilton
Creator
Creator

Sort issues after converting dates into Q and Months

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

1 Solution

Accepted Solutions
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

View solution in original post

1 Reply
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