View the original community article here
Last tested: Sep 6, 2019
One reason why your NDT rows and your corresponding explore query rows may not be matching up is that your NDT is not doing the same timezone conversion. What this means is that aggregations like count distinct that get dimensionalized in your NDT may not be the same as your original explore query values, in particular when the NDT is persistent.
In order to make sure that generated SQL of your persistent NDT includes the desired timezone conversion, you need to add in a timezone parameter under the explore_source parameter of your generated NDT LookML. Here's an example of the timezone parameter in action:
view: example_ndt {
derived_table: {
explore_source: base_explore {
timezone: "query_timezone"
column: time_field {}
column: count_distinct_field {}
}
}
dimension: time_field {
type: date_date
convert_tz: no
}
measure: count_distinct_field {
type: sum
}
}
Note: if you DO NOT include convert_tz:no in your time_field dimension, querying the time_field will result in a double time conversion (i.e. your dates will not match the original query).
This content is subject to limited support.