topic Sorting values that contain both text and numbers in QlikView

Sorting values that contain both text and numbers

Thu, 01 Oct 2015 11:11:08 GMT

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

Re: Sorting values that contain both text and numbers

tresB — Thu, 01 Oct 2015 11:53:36 GMT

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.

Re: Sorting values that contain both text and numbers

petter — Thu, 01 Oct 2015 11:54:31 GMT

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...

Re: Sorting values that contain both text and numbers

Thu, 01 Oct 2015 12:22:27 GMT

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

Re: Sorting values that contain both text and numbers

petter — Thu, 01 Oct 2015 12:27:13 GMT

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

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

Re: Sorting values that contain both text and numbers

petter — Thu, 01 Oct 2015 12:31:32 GMT

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

"Qlikview Components" Open Source Project