Good Day Looker Community
Hoping to get some assistance with the below trigger that I have created.
Goal: Looker currently has no native functionality to schedule a Look or Dashboard on the last day of the month.
My goal is to create a data trigger that will allow me to schedule a Look or Dashboard to run on the last day of every month at 2 am.
Tried Approaches:
Approach 1:
datagroup: monthly_at_two_am_on_last_day_test_1 {
sql_trigger: SELECT DATEADD(hour, 2, date_trunc('month', CURRENT_DATE) + interval '1 MONTH - 1 DAY');;
}
Approach 2:
datagroup: monthly_at_two_am_on_last_day_test_2 {
sql_trigger: SELECT DATEADD(hour, 2,DATE_TRUNC('month', GETDATE())+ interval '1 MONTH - 1 DAY');;
}
Challenges:
When I setup a look or dashboard to use the above triggers, the schedule runs on the 1st day of the next month at 2 am instead of the last day of the month at 2am.
So theres a 24 hour delay.
Additional info:
The data triggers are created in a '.model' file.
I then go to the report that I need scheduled and select the data-group from the drop down.
I have played around with the Delivery Timezone option when scheduling.
Any suggestions would be appreciated.
Hello @DataShanigans ,
Thank you for your interest in Looker.
To invalidate your cache on the last day of the month, the Datagroup below should work as it checks if the month for your Current date + 1 day changes,
datagroup: default_caching_policy {
sql_trigger: SELECT (EXTRACT(MONTH FROM DATEADD( day, 1, GETDATE()))) ;;
}