User Guide Getting Started Help Center Documentation Community Training
New LookML
Old LookML
New LookML
Dimension, Filter, and Parameter Types

This section refers to the type parameter that is part of a dimension or filter.

type can also be used as part of a measure, described on this documentation page.

type can also be used as part of a dimension_group, described on this documentation page.

Usage

view: view_name {
  dimension: field_name {
    type: field_type
  }
}

Hierarchy

type

Possible Field Types

Dimension, Filter, Parameter

Accepts

A dimension, filter, or parameter type

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

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

Type Definitions

D = Dimension
DG = Dimension Group
F = Filter
P = Parameter
Type Description Valid Field Types
date For fields that contain dates D F P
date_time For fields that contain dates and times D F P
distance For fields that calculate a distance between two type: location dimensions D
location For fields that are based on a latitude and longitude and will be used in visualizations D
number For fields that contain numbers D F P
string For fields that contain letters or special characters D F P
tier For fields that group numerical values into several ranges D
time Used with a dimension_group to create several time-based dimensions from a single table column DG
unquoted For parameter fields whose values will be inserted directly into SQL, and therefore should not be quoted (as they would with type: string) P
yesno For fields that show if something is true or false D F P
zipcode For fields that contain a zip code and will be used in visualizations D
Individual Time and Date Types A rarely used alternative to type: time for creating single, time-based dimensions D
int REMOVED5.4 Replaced by type: number D

distance

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 usage is:

view: view_name {
  dimension: field_name {
    type: distance
    start_location_field: field_name_1
    end_location_field: field_name_2
    units: kilometers
  }
}

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

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 }

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 f

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 or Static Map (Points) visualization (use a state or country field for Static Map (Regions)), or that you want to use in a type: distance calculation.

The usage pattern is:

view: view_name {
  dimension: field_name {
    type: location
    sql_latitude:${field_name_1} ;;
    sql_longitude:${field_name_2} ;;
  }
}

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} ;; }

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:

Supported Database Dialects for location

Looker’s ability to provide type: location depends on the database dialect’s functionality. The following list shows which dialects support type: location in the most recent Looker release:

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 }

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.

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}) ;; }

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

tier

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

The usage pattern is:

view: view_name {
  dimension: field_name {
    type: tier
    tiers: [numeric_value, numeric_value, … ]
    style:interval
    sql: ${my_field_name} ;;
  }
}

The sql parameter for type: tier 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} ;; }

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. Although not shown in the examples below, if there were negative numbers in the data, there would be a beginning tier that would include all numbers from negative infinity up to but not including 0. There are four possible values:

classic

style: classic is the default, and looks like:

interval

style: interval is similar to style: classic, but does not have the leading TXX labels. It looks like:

integer

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:

relational

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" }

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

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 on this documentation page.

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: 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 ;; }

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 documentation page, which also includes information on the timeframes, convert_tz, and datatype parameters.

unquoted

type: unquoted is used only with parameter fields. The unquoted type is similar to type: string, except that when the value of the parameter is inserted into the {% parameter %} liquid variable it will not be quoted. This is useful when inserting values into SQL, such as column or table names, that cannot be quoted in order to work properly.

Inserting unquoted values directly into SQL could create the possibility of unwanted SQL actions. To address this, type: unquoted parameters are restricted to the characters A through Z and 0 through 9 (no spaces or other special characters).

As an example, the following LookML creates a parameter called Table Name that will produce an unquoted value:

parameter: table_name { type: unquoted }

yesno

type: yesno creates a field that indicates if something is true or false. The values appear as yes and no in the Explore UI.

The sql parameter for a type: yesno dimension takes a valid SQL expression that evaluates to TRUE or FALSE. If the condition evaluates to TRUE, yes is displayed to the user, otherwise no is displayed.

The SQL expression for type: yesno dimensions cannot include any aggregations. This means it cannot contain SQL aggregations or any references to LookML measures. If you want to create a yesno field that includes a SQL aggregation or that references a LookML measure, use a measure with type: yesno, not a dimension.

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

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

If you want to reference a type: yesno field in another field, you should treat the type: yesno field as a boolean (in other words, 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) ;; }

If you use type: yesno with time-based data, the dimension returns yes if the datetime has a value, and returns no if not.

zipcode

type: zipcode is used with zip code dimensions that you want to plot on a Static Map (Points) visualization (use a state or country field for Static Map (Regions)). Any dimension of type: zipcode is automatically given the map_layer_name of us_zipcode_tabulation_areas. If you do not want to plot the zip codes, 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 five-digit, US zipcode.

For example:

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

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: [week, month, year] sql: ${TABLE}.created_at ;; }

… is logically equivalent to the following:

dimension: created_week { type: date_week sql: ${TABLE}.created_at ;; } dimension: created_month { type: date_month sql: ${TABLE}.created_at ;; } dimension: created_year { type: date_year sql: ${TABLE}.created_at ;; }

Available Time-Based Types

The following types are used in the type parameter of an individual dimension to create time or date based fields. Do not use these types with the timeframe parameter, which is documented here.

All individual time and date types require a timestamp as input from your database.

Special Types

Type Description Example Output
date_raw The raw value from your database, without casting or time zone conversion, will not show up on Explore page (typically not needed except in joins or time comparisons) 2014-09-03 17:15:00 +0000

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 Datetime truncated to the nearest second 2014-09-03 17:15:00
date_millisecond 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 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 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 Date of the underlying field 2017-09-03
date_date REMOVED4.6 Replaced by date

Week Types

Type Description Example Output
date_week Date of the week starting on a Monday of the underlying datetime 2017-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 2017-09
date_month_num Integer number of the month of the underlying datetime 9
date_month_name Name of the month September
date_day_of_month Day of month 3
date_fiscal_month_num Integer number of the month of the underlying datetime 9

To use date_fiscal_month_num type, the fiscal_month_offset parameter must be set in the model.

Quarter Types

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

To use date_fiscal_quarter and date_fiscal_quarter_of_year types, the fiscal_month_offset parameter must be set in the model.

Year Types

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

To use the date_fiscal_year type, the fiscal_month_offset parameter must be set in the model.

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:

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:

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.

Time Zones and convert_tz

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

Looker has various time zone settings that convert time-based data between different time zones. Looker converts time zones by default. If you do not want Looker to perform a time zone conversion for a particular dimension or dimension group, you can use the convert_tz parameter described on this documentation page.

Top