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

This page 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
duration ADDED6.0 Used with a dimension_group to create several duration-based dimensions from a single table column. For information about dimension groups, see this documentation page. DG
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. For information about dimension groups, see this documentation page. 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 F
Individual Duration Types ADDED6.0 A rarely used alternative to type: duration for creating single time-based dimensions that calculate time differences 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 field name by themselves, like view_name.field_name.

duration

New in Looker 6.0 is the ability to create a dimension group that is of type: duration. You can use a duration dimension group to calculate time differences between dimensions and/or SQL expressions.

type: duration is used in conjunction with a dimension_group to create a set of calculated time differences between dimensions and/or SQL expressions.

type: duration only works with a dimension_group and will not work with a regular dimension. However, you can specify individual duration-based dimensions, as explained in this section below.

For information about dimension groups with type: duration, see this documentation page.

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 fields, 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 the 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 in this section below.

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

As an example, for this dimension_group definition:

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

You can use this as a logic equivalent:

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.

Individual Duration Types

New in Looker 6.0 is the ability to create a dimension with duration types. You can use a duration dimension to calculate time differences between dimensions and/or SQL expressions.

Typically durations are handled as a dimension_group that uses type: duration.

However, it is possible to create one dimension for each individual duration you want to include, instead of generating all of them in a single dimension_group. This is generally avoided, unless you want to change Looker’s timeframe naming convention (such as having a field named Number of Days to Delivery instead of Duration to Delivery).

In such a situation there are several individual duration types that are listed below.

When you use a duration type for a dimension, you must also include the sql_start and sql_end parameters to provide the start and end times for calculating the time difference.

The sql_start and sql_end parameters can take any valid SQL expression that contains data in a timestamp, datetime, date, epoch, or yyyymmdd format. The sql_start and sql_end fields can be any of the following:

As an example, for this dimension_group definition:

dimension_group: to_delivery { type: duration intervals: [day, hour] sql_start: ${created_raw} ;; sql_end: ${delivered_raw};; }

You can use these dimension parameters as a logical equivalent:

dimension: number_of_days_to_delivery { type: duration_day sql_start: ${created_raw} ;; sql_end: ${delivered_raw};; } dimension: number_of_hours_to_delivery { type: duration_hour sql_start: ${created_raw} ;; sql_end: ${delivered_raw};; }

In the Explore UI, this would create dimensions called Number of Days to Delivery and Number of Hours to Delivery.

Available Duration Types

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

All individual duration types require a timestamp as input from your database.

Type Description Example Output
duration_day Calculates a time difference in days. 9 days
duration_hour Calculates a time difference in hours. 171 hours
duration_minute Calculates a time difference in minutes. 10,305 minutes
duration_month Calculates a time difference in months. 3 months
duration_quarter Calculates a time difference in quarters of the year. 2 quarters
duration_second Calculates a time difference in seconds. 606,770 seconds
duration_week Calculates a time difference in weeks. 6 weeks
duration_year Calculates a time difference in years. 2 years

Top