1 Reply Latest reply: Dec 2, 2014 12:01 PM by Josh Good RSS

    Sorting problem in Qlik Sense

      The code below extracts position 3 to 7 from a date (i.e. it returns 14-12 from the date 2014-12-02).

       

      left(right(date(DATE_COLUMN),8),5)

       

      When sorting, the result is mixed up and regardless of if I sort numerically or alphabetically it doesn't work (picture).


      The thing is also that the same code was used in Qlik View and the sorting worked well there.


      Any one has an idea?


      Capture.PNG.png

        • Re: Sorting problem in Qlik Sense
          Josh Good

          Hi Marcus,

           

          I would recommend using the MonthStart function combined with the Date function.  This will keep field as a date and give you the display you want (you can even display months with words).  Typically I do this in the script, here is a sample

           

          Load

          ....

          DATE_COLUMN,

          Date(MonthStart(DATE_COLUMN),'YY-MM') as Year-MonthNo,  //this will give you 14-02 format

          Date(MonthStart(DATE_COLUMN),'YY-MMM') as Year-MonthNo,  //this will give you 14-Feb format

          ,,,,

          SQL Select

          ....

          ;

           

          Please mark this as helpful or correct if appropriate so others know the status of the discussion.

           

          -Josh

          Qlik