Schedule with cron Pattern
I need to run the workbook on the last calendar day of every month. What shall be the cron pattern for it as the day of the month can be 28,29,30 or 31.
-
Hi Betty,
This is a common question, and weakness in cron. It doesn't have a special identifier for 'last day of the month.'
The general recommendation is have the job run on the first of each month, but configure the workbook to run against the last month's data via filter.
A second option is to simply schedule a job to run on the 28th, 29th, 30th, and 31st like so:
59 23 28-31 * *
The third and final suggestion I have is create four copies of the workbook, each with a cron pattern that specifies the months and end date explicitly.
February:
59 23 28 2 *
April, June, September, November:
59 23 30 4,6,9,11 *
January, March, May, July, August, October, December:
59 23 31 1,3,5,7,8,10,12 *
-
Thankyou for the options. I only problem witl be in Feb for leap year
Can you please be more expicit by " the job run on the first of each month, but configure the workbook to run against the last month's data via filter. " ie. I have the Cron pattern as 0 1 1 * * to be run at 1am on the 1st of each month and have all the dates pointing to last business day of the month .
-
Hi Betty,
If using advanced filtering, you can do a filter such as TODAY() - 1d and any execution on the first of the month would then be using a filter for whatever the last day of the month is.
So if you have a Date column, you can do something like this:
( #Date <= TODAY() - 1d ) && ( #Date >= STARTOFMONTH(TODAY() - 1d) )
This way anything run on the first of the month will subtract one day and you'll get the last month's end date. Then you can also use that same calculation in the STARTOFMONTH function to get the start of the month.
Please sign in to leave a comment.
Comments
4 comments