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

Announcements
Only at Qlik Connect! Guest keynote Jesse Cole shares his secrets for daring to be different. Learn More!
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