Sorting values that contain both text and numbers

[#1]

I have a field as below which works out the fiscal quarter :

'Q'& Ceil(If(Month($(vDate)) > 3, Month($(vDate)) - 3, Month($(vDate)) + 9) / 3) as Fiscal_Quarter

The values returned ie Q1,Q2,Q3,Q4 currently appear in a jumbled up order ( due to the 'Q' ) even though it has been sorted in ascending order. How do I get the values to appear in the correct ascending order ? Do I need to modify the script line or would it be a change in the 'Sort by' options ?

Thanks

[tresesco]

Create a Dual field instead like:

Load

          Dual(Fiscal_Quarter, QuarterStart($vDate)) as DFiscal_Quarter;             // preceding load

Load

          'Q'& Ceil(If(Month($(vDate)) > 3, Month($(vDate)) - 3, Month($(vDate)) + 9) / 3) as Fiscal_Quarter

From <>;

And then use this new dual field in your chart which would sort correctly.

[petter · Accepted solution]

Dual(

'Q'& Ceil(If(Month($(vDate)) > 3, Month($(vDate)) - 3, Month($(vDate)) + 9) / 3)

, Ceil(If(Month($(vDate)) > 3, Month($(vDate)) - 3, Month($(vDate)) + 9) / 3

as Fiscal_Quarter

Will give you a Dual field that use Q1, Q2, Q3, Q4 as display values in the UI and sort according to the Quarter number in list boxes and other places...

[#4]

Worked perfect with the dual function :

Similarly how would this work if using the pick match function ? This time for fiscal year :

    pick( WildMatch((If(Month($(vDate)) > 3, Year($(vDate))+1, Year($(vDate)))),'*2013*','*2014*','*2015*', ),

    'FY 13','FY 14','FY 15') as FYear

[petter]

I would advice you to have a look at this document:

https://community.qlik.com/docs/DOC-7094

[petter]

Or have a look at the QlikView Components QVC which gives you all these "calculations" for free ...

"Qlikview Components" Open Source Project