I’m struggling with creating a saved search formula that will give me the rolling 12 months (if run today, it will cover Jan 1, 2023 to Dec 31, 2023 (last day of the current month)). I have tried a couple ones including this one but I have not been very successful: CASE WHEN {transaction.trandate} BETWEEN ADD_MONTHS(ADD_MONTHS(TRUNC(SYSDATE, ‘MONTH’), -12), -1) AND ADD_MONTHS(TRUNC(SYSDATE, ‘MONTH’), -1) THEN 1 ELSE 0 END

Try CASE WHEN {transaction.trandate} BETWEEN ADD_MONTHS(TRUNC(SYSDATE, 'MM'), -12) AND LAST_DAY(ADD_MONTHS(TRUNC(SYSDATE, 'MM'), -1)) THEN 1 ELSE 0 END

CASE WHEN {transaction.trandate} BETWEEN TO_DATE(TO_CHAR(ADD_MONTHS(TRUNC(SYSDATE, 'MONTH'), -12), 'YYYY-MM-DD'), 'YYYY-MM-DD') AND TO_DATE(TO_CHAR(ADD_MONTHS(TRUNC(SYSDATE, 'MONTH'), -1), 'YYYY-MM-DD'), 'YYYY-MM-DD') THEN 1 ELSE 0 END you just have to add the dates .

Maybe date = this year in the criteria is enough?

Thanks for your ideas. This one below worked for me: CASE WHEN {transaction.trandate} BETWEEN ADD_MONTHS(LAST_DAY(TRUNC(SYSDATE, 'MM')), -12)+1 AND LAST_DAY(TRUNC(SYSDATE, 'MM')) THEN 1 ELSE 0 END

If you're doing a rolling 12 months, consider what using

SYSDATE

is going to do when you come into a new month (keeping in mind that it's the server's time, PST). It might be worth using

{today}

instead

Thanks, @David B. What time zone is used for {today}?

it's the user's timezone. so whoever's running the search. See Oracle Docs - Formulas-in-Search

Great. Thank you