1 Reply Latest reply: Aug 27, 2018 12:48 AM by Petter Skjolden RSS

    Qliksense Sorting not works during page loading

    haifeng zhao

      Hi All,

       

      Please see my challenge as below case, Thanks.

       

      Now dimension:

      if(GetFieldSelections(Periods)='Qtr',Qtr,[M o n t h])

      Custom sorting:

      if(GetFieldSelections(Periods)='Qtr',

      Match(Qtr,'Q1','Q2','Q3','Q4'),

      Match([M o n t h],'Jan','Feb','Mar','Apr','May','Jun','Jul','Aug','Sep','Oct','Nov','Dec'))

      and other expression sorting was sorting on load order.

       

      But sometimes, for example during the page loading , the order not works fine , also some other selections the order not works fine.

      Is there any way I can do this custom sorting? Thanks.


      Best Regards,

      Haifeng

        • Re: Qliksense Sorting not works during page loading
          Petter Skjolden

          You can avoid custom sorting by doing either of these two things taking as an example your case of months and quarters:


          1. 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.
          2. 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():

          https://help.qlik.com/en-US/sense/June2018/Subsystems/Hub/Content/Scripting/FormattingFunctions/Dual.htm

           

          Text():

          https://help.qlik.com/en-US/sense/June2018/Subsystems/Hub/Content/Scripting/InterpretationFunctions/Text.htm

           

          Num():

          https://help.qlik.com/en-US/sense/June2018/Subsystems/Hub/Content/Scripting/FormattingFunctions/Num.htm