Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Want to see what's currently in public preview? We've pulled it all together in one place. Check it out here
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

QlikSense - sort by expression with Overdue...

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.

0 Replies