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 the Measure types documentation page.

type can also be used as part of a dimension group, described on the dimension_group parameter documentation page.

Usage

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

bin

type: bin is an alias for type: tier. The two types can be used interchangeably.

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

The usage pattern is:

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

The age example above might look like this:

The way that this would appear in the Looker UI is described in the style section for the type: tier parameter on this page.

The bin type is an alias for type: tier. The two types can be used interchangeably, and the behavior is the same for both:

• The style subparameter is used to customize the appearance of bins in the Looker UI.
• Dimensions of type: bin cannot be used in custom filters.
• Using type: bin in conjunction with dimension fill can result in unexpected tier buckets.

distance

type: distance is used to calculate the distance of the most direct path (“as the crow flies”) 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:

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:

To note, the distance calculated will be of the most direct path (“as the crow flies”) between the two points, and not necessarily the distance traveled by road.

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

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 works only 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 the dimension_group parameter 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:

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:

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

For Looker to support type: location in your Looker project, your database dialect must also support it. The following table shows which dialects support type: location in Looker 22.6:

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 an 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):

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:

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

tier

You can use type: bin as an alias for type: tier. The two types can be used interchangeably.

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 with the style parameter.

The usage pattern is:

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

The age example above might look like this:

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

Dimensions of type: tier cannot be used in custom filters.

style

The style parameter lets you 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 (the default value)
• interval
• integer
• relational

classic

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
• T09 [80,inf) is the range including 80, and up to infinity

interval

style: interval is similar to style: classic, but it 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:

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

Things to consider

Using tier in conjunction with dimension fill can result in unexpected tier buckets.

For example, a dimension of type: tier, Age Tier, will display tier buckets for Below 0 and 0 to 9 when dimension fill is enabled, although the data does not include age values for those buckets:

When dimension fill is disabled for Age Tier, the buckets more accurately reflect the age values that are available in the data:

You can enable or disable dimension fill by hovering over the dimension name in the Explore, clicking the field-level gear icon, and selecting either Remove Filled in Tier Values to disable, or Fill in Missing Tier Values to enable.

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 works only 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 the dimension_group parameter documentation page, which also includes information on the timeframes, convert_tz, and datatype parameters, as well as common challenges and caveats to consider when you are using time-based data.

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, parameter values of type: unquoted 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:

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:

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:

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 the purposes of filtering on a zipcode dimension, some database dialects require that the database field referenced by the zipcode dimension is a varchar or string type field, not an integer type field.

For example:

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:

You can use this as a logic equivalent:

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

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

Special types

Time types

Date types

Week types

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

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

Quarter types

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

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:

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

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 the convert_tz parameter documentation page.

Dialect support for milliseconds and microseconds

Looker supports precision to microseconds; however, some databases support precision only to seconds. If a database encounters a time type more precise than it can support, it will round up to seconds.

In Looker 22.6, the following dialects support milliseconds:

In Looker 22.6, the following dialects support microseconds:

Individual duration types

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:

• A reference to a raw timeframe from an existing dimension group of type: time.
• A reference to a dimension of type: date_raw.
• A SQL expression that is a timestamp, such as a reference to a SQL column that is a timestamp.
• A SQL expression that pulls a time from your database, using the appropriate expression for your dialect.

As an example, for this dimension_group definition:

You can use these dimension parameters as a logical equivalent:

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

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