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.
