Question

Using getdate for a dimension/measure calculation breaks scheduling

  • 18 September 2018
  • 4 replies
  • 1008 views

The dimension “Contract Days Remaining”; uses a relative date function getdate() to compute it’s value. The getdate() function will change in value each day:


datediff(day, getdate(), (CONVERT(VARCHAR(10),renewal_date ,120))) AS "contract_days_remaining"

Because this figure changes everyday, should we schedule a report that contains this dimension using “and results changed since last run”, then the report is sent every day. We have no way to schedule a report to be sent but only when new data has been added to the DB.



4 replies

Hi Peter,


Is it necessary that you use the getdate ? you can also get a date diff with table calculation using the function diff_days(start_date, end_date) It returns the number of days between start_date and end_date.

Additionally we could create a calculation in the Explore of the Look you are scheduling, hiding the dimension/measure that contains the getdate from the visualisation, and using the function type if(yesno_expression,value_if_yes, value_if_no) to ‘yes’ when the measure changes data, and ‘no’ when the data has not changed. This will allow the “and results changed since last run” to work as expected.

If you have any questions on this, come to our chat support for more help.

We can’t have a calculated field as this isn’t possible to include in a Lookml dashboard.

The reason we use getdate instead of diff_days(start_date, end_date) is that we want the calculation to be from the current date (whatever that is) and the renewal date.

We have considered datagroups, however there is no control over the scheduling which means the reports would be issued before the filters have been updated for report period. We have a custom script that updates user attributes which are then set as defaults for the report period filter.

Userlevel 1

Hey @pmccabe,


I hope all is well! If we are referring to a dashboard, then the option to use a Send this schedule if advanced scheduling option is currently unavailable. Of course, I’ll be sure to relay this feedback to the product team on my end. With that said, we can schedule this dashboard with a delivery schedule that aligns with when we expect information to be updated and the changing results of that datediff field shouldn’t influence it.


Regarding the table calculation on a LookML dashboard. In case this can help achieve your goal and you would like to do some testing, it looks like we can include table calculations in LookML dashboards. I tested this out just to confirm. My LookML Dashboard file looks like this:


- dashboard: date_diff_test
title: Date Diff Test
layout: newspaper
elements:
- title: Date Diff Test
name: Date Diff Test
model: leticia_model_exercise
explore: orders
type: table
fields:
- orders.created_date
- orders.days_since_order
sorts:
- orders.created_date desc
limit: 500
dynamic_fields:
- table_calculation: calc_date_diff
label: Calc Date Diff
expression: diff_days(${orders.created_date}, now())
value_format:
value_format_name:
_kind_hint: dimension
_type_hint: number
query_timezone: America/Los_Angeles
series_types: {}
row: 0
col: 2
width: 20
height: 17

and the resulting dashboard is below:



You’re absolutely right regarding how the results changed since last run will influence the schedule for a Look with that datediff field. So the best bet here will probably be keeping this as an element on a dashboard and scheduling that dashboard with a Repeating interval trigger or Datagroup Update trigger. Perhaps a datagroup, with a sql_trigger that is set for a time after the custom user attributes script runs, can be created if that script runs at a certain time (or times) a day. Alternatively, we can define a Repeating Interval trigger and set a time to send for after that script is expected to run.


I hope this helps! If you’d like us to take a closer look at your example, feel free to visit us at help.looker.com or contact us via chat support!


Cheers,


Leticia

This isn’t really a suitable solution for our setup. We have extensively tested the datagroup function but because we have no way of controlling the time for the report to be run with datagroup then the report schedule runs before the script we use to run our report period filters has had a chance to run, thus the scheduled report contains out of date data. We are testing liquid html and may have a solution.

Reply