Schedule with cron Pattern

Comments

4 comments

  • Alan

    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 *

     

    0
    Comment actions Permalink
  • Betty Modi

    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  .

    0
    Comment actions Permalink
  • Alan

    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.

    0
    Comment actions Permalink
  • Betty Modi

    Thankyou

    0
    Comment actions Permalink

Please sign in to leave a comment.