Hi, I am building a saved search which we are planning to schedule on a daily basis. I want the criteria to show previous months this fiscal year. which works fine for all months except Jan. Since the fiscal year changes, it shows a null result. Since the search will be scheduled adter the books are finalized(in the first week of the following month), is it possible to add a criteria where it shows me the data for the previous fiscal year if the month is Jan and previous months in this fiscal year for all other months? I am relatively new to saved searches so would appreciate any help regarding it. Thanks!

why

previous months this fiscal year

can't you just do last month?

uses a criteria formula to make it clear that the trandate must be the transaction up to the previous month Use formula like this formula numeric = CASE WHEN {trandate} <= LAST_DAY(ADD_MONTHS({today}, -1)) THEN 1 ELSE 0 END

The idea behind it is to have a year to date report in a matrix format. I have the columns for the months based on formulae and was thinking that I can filter the transactions based on the criteria being previous months this fiscal year quick filter which works but I am having trouble with January. If I use the above formula, what criteria do I set? Is it a date formula?

Show me your actual result with your saved search and your formula

sorry to be the bearer of bad news, but the scheduling feature of saved searches doesn't work on a summary search.

I can schedule the search without any issues though. I receive a CSV output of the search daily. The only issue that I am facing is how do I make the criteria dynamic so that it generates an output for the previous fiscal year if the month is Jan

get rid of the criteria and do in in the case statement. CASE WHEN TO_CHAR{trandate}, 'YY' = TO_CHAR{today}, 'YY' -1 THEN {amount} ELSE 0 END CASE WHEN TO_CHAR{trandate}, 'YY' = TO_CHAR{today}, 'YY' AND TO_CHAR{trandate}, 'MM' = 01 THEN {amount} ELSE 0 END CASE WHEN TO_CHAR{trandate}, 'YY' = TO_CHAR{today}, 'YY' AND TO_CHAR{trandate}, 'MM' = 02 THEN {amount} ELSE 0 END CASE WHEN TO_CHAR{trandate}, 'YY' = TO_CHAR{today}, 'YY' AND TO_CHAR{trandate}, 'MM' = 03 THEN {amount} ELSE 0 END etc.....

Thanks, this worked perfectly!