Scheduling on the Last Day of Month

  • 22 June 2022
  • 0 replies
  • 251 views

Userlevel 5
Badge
  • Looker Staff
  • 172 replies

 

Looker will not be updating this content, nor guarantees that everything is up-to-date. 

 

When scheduling content in Looker, we can choose which day of month to send the Look:
 


If we choose the 31st of the month, then the schedule simply won't run on months with fewer than 31 days. So how do we select the last day of month?
 

Using a custom filter for Looks


For Looks, we can filter the Look so it only shows any results on the last day of month, and then tell the schedule to only send out on the last day of the month. Here is how:

  1. Create a Custom Filter

    The custom filter we'll apply here is: extract_days(add_days(1,now())) = 1

    The above expression returns true if tomorrow is the first of the month, which is another way of saying today is the last day of the month. This custom filter will filter out all results, unless today is the last day of the month.

  2. Schedule Only if There are Results

    Because there will only be results on the last day of the month, we can now schedule this Look to go out daily, but only send when there are results:


Using a datagroup trigger 


You can also use a variant of the custom filter method above, based on the ability to schedule on a datagroup trigger.

  1. Create a Datagroup

    We will make a datagroup with a trigger that only fires at the end of the month, using similar logic as the above custom filter. This example is in Redshift SQL, and may require slight adaptations for different databases.

    SELECT (EXTRACT(MONTH FROM DATEADD( day, 1, GETDATE())))

    This returns tomorrow's month. For the last day of the month, that's next month, so the datagroup gets triggered. For every other day, it's the same month, so the datagroup is not triggered.

  2. Schedule on the Datagroup

    Now, the datagroup can be selected in the schedule options. We will choose the desired datagroup from the dropdown:
     

     

That is it! Now we have a schedule which will send out on the last day of each month.


This topic has been closed for comments