Last Year, Current Formula in Saved Search

Requirement:

I want to do multiple case when and return the following

• If trandate is prior to Last year, return “LY”
• If trandate is current year, return “Current”
• if trandate is future years, return “LT”

Solution:

Please try the following:

• CASE WHEN {trandate} BETWEEN TO_DATE(TO_CHAR(ADD_MONTHS(SYSDATE, -12), ‘YYYY’) || ‘-01-01’, ‘YYYY-MM-DD’) AND TO_DATE(TO_CHAR(ADD_MONTHS(SYSDATE, -12), ‘YYYY’) || ‘-12-31’, ‘YYYY-MM-DD’) THEN ‘LY’
• WHEN {trandate} BETWEEN TO_DATE(TO_CHAR(SYSDATE, ‘YYYY’) || ‘-01-01’, ‘YYYY-MM-DD’) AND TO_DATE(TO_CHAR(SYSDATE, ‘YYYY’) || ‘-12-31’, ‘YYYY-MM-DD’) THEN ‘Current’
• WHEN {trandate} >= TO_DATE(TO_CHAR(ADD_MONTHS(SYSDATE, 12), ‘YYYY’) || ‘-01-01’, ‘YYYY-MM-DD’) THEN ‘LT’ END