4 Replies Latest reply: Oct 1, 2015 4:02 PM by Ian Robison RSS

    Sorting quarterly dates

    Ian Robison

      Hi,

       

      I am presented with dates in the form of Q1 2014, Q2 2014, etc. When covering multiple years the sorting in Qlik Sense does not give me the order by year. I have read some other posts on sorting and the suggestion has been to use the Dual function but I can't find very much documentation that shows how to deal with this.

       

      Referring to the image you can see how Q2 2015 is out of sequence. Any suggestions are very much appreciated.

       

      2015-09-29_14-03-32.jpg

       

      Regards,

      Ian

        • Re: Sorting quarterly dates
          Marcus Sommer

          I'm not sure if you could use sorting-expressions within qlik sense but if and you have connected various period-fields (maybe within a master-calendar, here you find many informations about this approach: How to use - Master-Calendar and Date-Values) you could use something like avg(YourDateField). Otherwise you need to create a dual-field:

           

          Syntax is: dual(StringValue, NumericValue) and in your case maybe this one:

           

          dual(YourQuarterField, Year * 10 + NumericQuarter)

               or if you hadn't such one

          dual(YourQuarterField, Year * 10 + ceil(num(month(YourDateField)) / 3))

           

          - Marcus

            • Re: Sorting quarterly dates
              Ian Robison

              Hi Marcus,

               

              Thanks you for replying so quickly. I should have clarified that my date information is formed as a single record in my Excel dump. Columns name is Quarter and each row is represented in the form of Q1 2015. When I try your example in a load script it errors out stating that Year is not found.

               

              I'm new to scripting so most of this is trial and error for me at this stage. Here is my original script if that sheds any light on what I am attempting.

               

              [Usage]:

              LOAD [Brand],

                  [Quarter],

              [Product],

              [Machine Version],

              [Ultimate],

              [Industry Code],

              [Country],

              [SELECT Series],

              [Sales Channel],

              [Sales Rep],

              [Unique Machines]

              FROM [lib://Dashboard/Plan.xlsx]

              (ooxml, embedded labels, table is Usage);

               

              Thanks again,

              Ian