Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
You can avoid custom sorting by doing either of these two things taking as an example your case of months and quarters:
- Make a field in your data model called Qtr# which contains the values 1,2,3,4 and occurs associated with the Q1, Q2, Q3 and Q4 values of the Qtr field. Then do the same for the Month field - make a field named Month# and match the numbers up with 'Jan'....'Dec'. If you have the Qtr and Month residing in a calendar table it is easy to generate the corresponding fields Qtr# and Month#. Whenever you want to specify sort order you refer to these fields even though you show the Qtr and Month field.
- The Qlik way - especially in the instance of quarters and months is to make use of the dual nature of all values in Qlik. So the quarter field can be made to contain both a text part and a numberic part for each value. The field would then always contain the values 'Q1',1 , 'Q2',2 , 'Q3,3' , 'Q4',4 which can be assigned like this Dual('Q1',1) which will result in the dual-value for quarter number one. For months it is even easier you can simply use the Month()-function on a date field and it will create a dual value for the corresponding month which will be 'Jan',1 for January and so forth. The beauty of dual-values is that they will sort by their numeric part and will by default show the text part.
All real dates in Qlik are dual in fact. So the date 27th of August 2018 has the text part according to your date format settings and the numeric part is 43339 (the number of days passed since 30th of December 1899).
You can create your own dual fields easily with the dual function as you saw above. If you have a quality field with for instance quality text descriptions and the ranking is not alphanumeric then you could to this:
Input:
Superior
High
Excellent
Medium
Low
Unacceptable
And you want them to sort correctly you would assign 1..6 in the order above.
In a load script this could be done explicitly like this:
LOAD
Dual( Quality , RowNo() ) AS Quality
INLINE [
Superior
High
Excellent
Medium
Low
Unacceptable
];
Calendar:
LOAD
Dual( Qtr , Match( 'Q1','Q2','Q3','Q4) ) AS Qtr, // you will still keep the text part but
Dual( Month , WildMatch('Jan*','Feb*',.......) AS Month // add a numeric part to sort out the sort order 
/* Or */
Month( InvoiceDate ) AS Month // will be a dual-valued month field
....
More information about useful dual-related functions and the dual nature of Qlik fields and values:
Dua():
Text():
Num():
