Dimension and Filter Types

LookML
Version

On this Page
Docs Menu

Go to Field Parameter List

This page includes additional details about the various types that can be assigned to a dimension or filter. A dimension or filter can only have one type, which defaults to string if no type is specified.

Some dimension and filter types have supporting parameters, which are described within the appropriate section.

Type Definitions

Type Description
string For dimensions that contain letters or special characters
int AVOID AS OF3.38 Replaced by type: number
number For dimensions that contain numbers
yesno For dimensions that will show if something is true or false
time Used with a dimension_group to create several time-based dimensions from a single table column
Individual Time and Date Types (date_week, date_month, etc.) A rarely used alternative to type: time for creating single, time-based dimensions
tier For dimensions that will group numerical values into several ranges
location For dimensions that will be based on a latitude and longitude and will be used in a visualization
distance ADDED3.44 For dimensions that calculate a distance between two type: location dimensions
zipcode For dimensions that contain a zip code and will be used in a visualization

string

type: string is typically used with fields that contain letters or special characters. It can be used with number fields as well, although Looker has better features for handling numbers if you use type: number instead.

The sql parameter for type: string dimensions can take any valid SQL expression.

For example, the following LookML creates a field Full Name by combining a field called First Name and Last Name:

- dimension: full_name type: string sql: CONCAT(${first_name}, ' ', ${last_name})
dimension: full_name { type: string sql: CONCAT(${first_name}, ' ', ${last_name}) ;; }

In this example type: string could be omitted, because string is the default value for type.

int AVOID AS OF3.38

As of version 3.38, use type: number instead.

type: int is used with numbers that you want to display with no commas or decimal places. The underlying data does not necessarily have to be an integer; it can also be a floating point number.

The sql parameter for type: int dimensions can take any valid SQL expression that results in a number or integer.

For example, the following LookML takes the value from the product_id column and formats it as an integer:

- dimension: product_id type: int sql: ${TABLE}.product_id
dimension: product_id { type: int sql: ${TABLE}.product_id ;; }

Note: A dimension can only perform arithmetic on other dimensions, not measures.

number

type: number is used with numbers or integers.

The sql parameter for type: number dimensions can take any valid SQL expression that results in a number or integer.

type: number fields can be formatted by using the value_format or value_format_name parameters.

For example, the following LookML creates a field called Profit based on the Revenue and Cost field, then displays it in a money format ($1,234.56):

- dimension: profit type: number sql: ${revenue} - ${cost} value_format_name: usd
dimension: profit { type: number sql: ${revenue} - ${cost} ;; value_format_name: usd }

Note: A dimension can only perform arithmetic on other dimensions, not measures. Additionally, type: number dimensions will not provide suggestions to users, even if you are using them to display ID numbers.

yesno

type: yesno is used with fields that will say if something is true or false. The values appear as yes and no in the Explore UI.

The sql parameter for type: yesno dimensions can take any valid SQL expression that evaluates to TRUE or FALSE. If the condition evaluates to TRUE, yes will be displayed to the user, otherwise no will be displayed.

For example, the following LookML creates a field that says whether or not an order has been paid, based on the Status field:

- dimension: is_order_paid type: yesno sql: ${status} = 'paid'
dimension: is_order_paid { type: yesno sql: ${status} = 'paid' ;; }

If you want to reference a type: yesno field in another field, you should treat it as a boolean (i.e. as if it contains a true or false value already). For example:

- dimension: is_big_order type: yesno sql: ${order_size} = 'big'   # This is correct - measure: total_boxes_needed type: number sql: SUM(CASE WHEN ${is_big_order} THEN 2 ELSE 1 END)   # This is NOT correct - measure: total_boxes_needed type: number sql: SUM(CASE WHEN ${is_big_order} = 'Yes' THEN 2 ELSE 1 END)
dimension: is_big_order { type: yesno sql: ${order_size} = 'big' ;; } # This is correct measure: total_boxes_needed { type: number sql: SUM(CASE WHEN ${is_big_order} THEN 2 ELSE 1 END) ;; } # This is NOT correct measure: total_boxes_needed { type: number sql: SUM(CASE WHEN ${is_big_order} = 'Yes' THEN 2 ELSE 1 END) ;; }

time

type: time is used in conjunction with a dimension_group and the timeframes parameter to create a set of time-based dimensions. For example, you could easily create a date, week, and month dimension based on a single timestamp column.

type: time only works with a dimension_group and will not work with a regular dimension. However, you can specify individual time-based dimensions as explained here.

The sql parameter for type: time dimension groups can take any valid SQL expression that contains data in a timestamp, datetime, date, epoch, or yyyymmdd format.

The form of a dimension group of type: time is:

- dimension_group: dimension_group_name type: time timeframes: [timeframe, timeframe, …] # valid timeframes described below sql: SQL expression datatype: epoch | timestamp | datetime | date | yyyymmdd # defaults to datetime convert_tz: true | false
dimension_group: dimension_group_name { type: time timeframes: [timeframe, timeframe, …] # valid timeframes described below sql: SQL expression ;; datatype: epoch| timestamp | datetime | date | yyyymmdd # defaults to datetime convert_tz: yes | no }

As an example, suppose you had a column named created_at that contained datetime information. You want to create a date, week, and month dimension based on this datetime. You could use:

- dimension_group: created type: time timeframes: [date, week, month] sql: ${TABLE}.created_at
dimension_group: created { type: time timeframes: [date, week, month] sql: ${TABLE}.created_at ;; }

In the Explore UI this would generate 3 dimensions with the names Created Date, Created Week, and Created Month. Note how the dimension_group name is combined with the timeframes to generate the dimension names.

If you want to refer to one of these dimensions in another LookML field, you would use the reference ${created_date}, ${created_week}, or {$created_month}. If you try to use just ${created}, Looker will not know which timeframe you are referring to and an error will result.

For this same reason, you should not use the primary_key parameter on a dimension group if you specify more than one timeframe.

For more information about dimension groups, see this page, which also includes information on the timeframes, convert_tz, and datatype parameters.

Individual Time and Date Types

Typically dates are handled as a dimension_group that uses type: time.

However, it is possible to create one dimension or filter field for each individual time frame you want to include, instead of generating all of them in a single dimension_group. This is generally avoided, unless you have already pre-calculated time columns in your database or want to change Looker’s timeframe naming convention (such as having a field named Created Date of Purchase instead of Created Date).

In such a situation there are many individual time and date based types that are listed below.

For example, this dimension_group definition …

- dimension_group: created type: time timeframes: [date, week, month] sql: ${TABLE}.created_at
dimension_group: created { type: time timeframes: [date, week, month] sql: ${TABLE}.created_at ;; }

… is logically equivalent to the following:

- dimension: created_date type: date_date sql: ${TABLE}.created_at   - dimension: created_week type: date_week sql: ${TABLE}.created_at   - dimension: created_month type: date_month sql: ${TABLE}.created_at
dimension: created_date { type: date_date sql: ${TABLE}.created_at ;; } dimension: created_week { type: date_week sql: ${TABLE}.created_at ;; } dimension: created_month { type: date_month sql: ${TABLE}.created_at ;; }

Available Time-based Types

The following types can be used with individual dimensions to create time or date based fields.

Special Types

Type Description Example Output
date_raw The raw value from your database, without casting or timezone conversion, will not show up on Explore page (typically not needed except in joins or time comparisons) 2014-09-03 17:15:00 +0000
yesno A yesno dimension, returning “Yes” if the datetime has a value, otherwise “No” Yes

Time Types

Type Description Example Output
date_time Datetime of the underlying field (some SQL dialects show as much precision as your database contains, while others show only to seconds) 2014-09-03 17:15:00
date_time_of_day Time of day 17:15
date_hour Datetime truncated to the nearest hour 2014-09-03 17
date_hour_of_day Integer hour of day of the underlying field 17
date_hourX Splits each day into intervals with the specified number of hours. Requires explanation, see below. See Below
date_minute Datetime truncated to the nearest minute 2014-09-03 17:15
date_minuteX Splits each hour into intervals with the specified number of minutes. Requires explanation, see below. See Below
date_second ADDED3.54 Datetime truncated to the nearest second 2014-09-03 17:15:00
date_millisecond ADDED3.54 Datetime truncated to the nearest millisecond (not supported in Clustrix, Denodo, or MySQL prior to 5.6.4) 2014-09-03 17:15:00.000
date_millisecondX ADDED3.54 Splits each second into intervals with the specified number of milliseconds (not supported in Clustrix, Denodo, or MySQL prior to 5.6.4). Requires explanation, see below. See Below
date_microsecond ADDED3.54 Datetime truncated to the nearest microsecond (not supported in Clustrix, Denodo, or MySQL prior to 5.6.4) 2014-09-03 17:15:00.000000

Date Types

Type Description Example Output
date or date_date Date of the underlying field 2014-09-03

Week Types

Type Description Example Output
date_week Date of the week starting on a Monday of the underlying datetime 2014-09-01
date_day_of_week Day of week alone Wednesday
date_day_of_week_index Day of week index (0 = Monday, 6 = Sunday) 2

Please note that the date_week, date_day_of_week, and date_day_of_week_index types depend on the value of week_start_day, which defaults to Monday.

Month Types

Type Description Example Output
date_month Year and month of the underlying datetime 2014-09
date_month_num Integer number of the month of the underlying datetime 9
date_month_name ADDED3.48 Name of the month September
date_day_of_month Day of month 3

Quarter Types

Type Description Example Output
date_quarter Year and quarter of the underlying datetime 2015-Q3
date_quarter_of_year Quarter of the year preceded by a “Q” Q3

Year Types

Type Description Example Output
date_year Integer year of the underlying date time 2014
date_day_of_year Day of year 143
date_week_of_year Week of the year as a number 17

Using date_hourX

In date_hourX the X is replaced with 2, 3, 4, 6, 8, or 12.

This will split up each day into intervals with the specified number of hours. For example date_hour6 will split each day into 6 hour segments, which will appear like:

  • 2014-09-01 00:00:00
  • 2014-09-01 06:00:00
  • 2014-09-01 12:00:00
  • 2014-09-01 18:00:00

To give an example, a row with a time of 2014-09-01 08:03:17 would have a date_hour6 of 2014-09-01 06:00:00.

Using date_minuteX

In date_minuteX the X is replaced with 2, 3, 5, 10, 15, or 30.

This will split up each hour into intervals with the specified number of minutes. For example date_minute15 will split each hour into 15 minute segments, which will appear like:

  • 2014-09-01 01:00:00
  • 2014-09-01 01:15:00
  • 2014-09-01 01:30:00
  • 2014-09-01 01:45:00

To give an example, a row with a time of 2014-09-01 01:17:35 would have a date_minute15 of 2014-09-01 01:15:00.

Timezones and convert_tz

In general, time computations (differences, durations, etc.) only work correctly when you operate on time values that are all converted to the same timezone, so it is important to keep timezones in mind when writing LookML.

The Looker Admin page enables you to set a Database Time Zone and a Query Time Zone (see this Discourse article for more detail). If the same timezone is chosen for both of these settings, Looker does not perform any timezone conversions, and there is no need to use the convert_tz parameter.

However, if different timezones are chosen, Looker performs a timezone conversion for all time-based dimensions by default. If you do not want Looker to perform a timezone conversion for a particular dimension or dimension group, you can use the convert_tz parameter described here.

tier

type: tier is used in conjunction with the tiers parameter to separate a numeric field into a set of number ranges. For example, you might tier an age field into different age ranges. You can use the style parameter to change how the tiers appear in the Looker UI.

The sql parameter for type: number dimensions can take any valid SQL expression that results in a number or integer.

The age example above might look like this:

- dimension: age_tier type: tier tiers: [0,10,20,30,40,50,60,70,80] style: classic # the default value, could be excluded sql: ${age}
dimension: age_tier { type: tier tiers: [0, 10, 20, 30, 40, 50, 60, 70, 80] style: classic # the default value, could be excluded sql: ${age} ;; }

The way that this would appear in the Looker UI is described in the style section below.

style

The style parameter enables you to change the way that tiers appear in the Looker UI. There are four possible values:

  • style: classic is the default, and looks like:

  • You can interpret this tier notation as follows:
    • T02 [10,20) is the range including 10, and up to but not including 20
    • T02 [80,inf) is the range including 80, and up to infinity
  • style: interval is similar to style: classic, but does not have the leading TXX labels. It looks like:

  • style: integer must be used with discrete integer values (such as age). If you try to use non-integers to define the tiers you will receive an error. This style looks like:

  • style: relational is best used with continuous numbers (such as dollars) and looks like:

You can also style tiers with value_format. For example:

- dimension: amount_tier type: tier tiers: [0,10,20,30,40,50,60,70,80] style: integer sql: ${amount} value_format: '$#,##0'
dimension: amount_tier { type: tier tiers: [0, 10, 20, 30, 40, 50, 60, 70, 80] style: integer sql: ${amount} ;; value_format: "$#,##0" }

This example would result in tier labels like $10 to $19, $20 to $29, etc.

location

type: location is used in conjunction with the sql_latitude and sql_longitude parameters to create coordinates that you want to plot on a Map Points visualization (use a state or country field for Map Regions), or that you want to use in a type: distance calculation. If you do not want to plot the locations or calculate distances, you can use a simpler type such as type: number. When you view a location in a table, it will show the value from your database, as well as automatically generate a link to that location in Google Maps:

The sql parameter for type: location dimensions is excluded. Instead, you supply any valid SQL expression that results in a decimal latitude or longitude to the sql_latitude and sql_longitude parameters. These are usually references to LookML fields that contain latitude or longitude information, but they can be static values if you wanted to have a location of your headquarters, or something along those lines.

For example, you might create a Store Location dimension like this:

- dimension: store_location type: location sql_latitude: ${store_latitude} sql_longitude: ${store_longitude}
dimension: store_location { type: location sql_latitude: ${store_latitude} ;; sql_longitude: ${store_longitude} ;; }

distance ADDED3.44

type: distance is used to calculate the distance between two type: location dimensions.

The sql parameter for type: distance dimensions is excluded. Instead, you supply a reference to a type: location dimension in the start_location_field and end_location_field parameters.

The unit of distance is determined by the units parameter, which can take the following values:

  • feet
  • kilometers
  • meters
  • miles
  • nautical_miles
  • yards

For example, you might calculate the distance traveled by a customer to pickup a rental like this:

- dimension: distance_to_pickup type: distance start_location_field: customer.home_location end_location_field: rental.pickup_location units: miles
dimension: distance_to_pickup { type: distance start_location_field: customer.home_location end_location_field: rental.pickup_location units: miles }

Note that you should not use the ${view_name.field_name} syntax in the start_location_field and end_location_field parameters. Instead, use the view name and field name by themselves, like view_name.field_name.

zipcode

type: zipcode is used with zip code fields that you want to plot on a Map Points visualization (use a state or country field for Map Regions). If you do not want to plot the zipcodes, you can use a simpler type such as type: number.

The sql parameter for type: zipcode dimensions can take any valid SQL expression that results in a 5 digit, US zipcode.

For example:

- dimension: zip type: zipcode sql: ${TABLE}.zipcode
dimension: zip { type: zipcode sql: ${TABLE}.zipcode ;; }

Still have questions?
Go to Discourse - or - Email Support
Top