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?
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:
- 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.
- 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:
For dashboards, we can use a variant of the custom filter method above, based on the ability to schedule on a datagroup trigger.
- 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.
- 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.