Why are my NDT values different when compared to an equivalent explore query?

Knowledge Drop

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).

Documentation

This content is subject to limited support.                

Version history
Last update:
‎05-07-2021 09:08 AM
Updated by: