0 Replies Latest reply: Apr 12, 2017 11:23 PM by Corey Edwards RSS

    QlikSense - sort by expression with Overdue...

    Corey Edwards

      I am trying to develop a custom sort by expression in a pivot table where:

      a. anything before today is in a bucket called 'Overdue...'

      b. the next three months are by their month name

      c. anything past three months is in a bucket called 'Future...'

       

      I have the figures in their respective buckets - 'Overdue...', 'APR-2017', 'MAY-2017', 'JUN-2017', 'Future...' using an expression in a column of the pivot:

      if(match(Tran_type,'O_BANK'),

        if(date(Monthname(SO_Promised_Date),'MMM-YYYY') < date(Monthname(today()),'MMM-YYYY'), 'Overdue...',

          if(date(Monthname(SO_Promised_Date),'MMM-YYYY') > date(Monthname(addmonths(today(),2)),'MMM-YYYY'), 'Future...',

          date(Monthname(SO_Promised_Date),'MMM-YYYY'))))

       

      However they are not sorting in the correct order.  They are currently sorting MAY-2017, APR-2017, Overdue..., Future..., JUN-2017.  I am using the expression:

      WildMatch(

      (if(match(Tran_type,'O_BANK'),

        if(date(Monthname(SO_Promised_Date),'MMM-YYYY') < date(Monthname(today()),'MMM-YYYY'), 'Overdue...',

          if(date(Monthname(SO_Promised_Date),'MMM-YYYY') > date(Monthname(addmonths(today(),2)),'MMM-YYYY'), 'Future...',

          date(Monthname(SO_Promised_Date),'MMM-YYYY'))))),

              'Overdue...', date(Monthname(SO_Promised_Date),'MMM-YYYY'), 'Future...')

       

      Can anyone see where I am going wrong?  I have been through so many posts but cannot find anything that works for me.