Notification

This Help Center is moving to Cloud. Learn more.

Use functions in calculated fields

Create more powerful calculated fields.

Functions let you manipulate your data in ways beyond doing simple arithmetic. Looker Studio provides over 50 functions, grouped into the following types:

  • Aggregation functions perform calculations over multiple rows of your data. Examples include SUM, AVG, MIN, MAX.
  • Arithmetic functions apply mathematical calculations to your data. Examples include LOG, POWER, ROUND.
  • Date functions let you manipulate and transform time data. Examples include DATE, DATETIME_DIFF, YEAR.
  • Geo functions let you transform geographic location data. Examples include TOCITY, TOCOUNTRY, TOREGION.
  • Text functions let you manipulate string data. Examples include CONCAT, REGEXP_MATCH, SUBSTR.

See the complete list of available functions.

Function arguments

All functions expect input, called arguments, that tell the function what data to act upon. Arguments can be field names or expressions. An expression can be a number, literal text, or a statement that evaluates to a field name in your data source. Arguments can also provide additional instructions or formatting information.

More about function arguments

  • When providing function arguments, be sure to enclose literal text in single or double quotes.
  • When using regular expressions, use double backslash to escape special characters. For example:
    REGEXP_MATCH(GotStars, "\\*")
     
    Matches the literal * character.
  • Geo functions require a supported geo code as input.

Geo input codes

  • 'CITY_ID'
  • 'CONTINENT_CODE'
  • 'COUNTRY_ISO_CODE'
  • 'REGION_ISO_CODE'
  • 'SUB_CONTINENT_CODE'

The valid geo codes are defined here:

The input code is optional if the field expression is a derived column already containing semantic geo information; otherwise it is required.

Function examples

Here are some simple examples of using the most popular functions. Additional complex examples are available in the individual function references.

Example 1: Organize and simplify your Analytics campaigns.

Suppose you run multiple ad campaigns, and use a coding system to identify them by category. Assuming the codes share a regular format, you could use the CASE expression and REGEXP_MATCH function to group related campaigns.

For example, say you've got campaign codes like C1, C2, and C3 for consumer goods, E1, E2, and E3 for electronics, S1, S2, and S3 for social ads. You could group these campaign codes into their respective category, and optionally, lump other campaigns into a miscellaneous category:

CASE
WHEN REGEXP_MATCH(CAMPAIGN, "C.*") THEN "Consumer"
WHEN REGEXP_MATCH(CAMPAIGN, "E.*") THEN "Electronics"
WHEN REGEXP_MATCH(CAMPAIGN, "S.*") THEN "Social"
ELSE "Misc"
END

You can now use this calculated field to see how your campaigns are performing per group, or use it in a filter control to refine the view of your data.

Learn more about CASE.

Example 2: Combine values from multiple fields.

You can aggregate and display your data in new ways by combining multiple fields via the CONCAT function. This creates a new field with the concatenated values. For example:

CONCAT('Browser: ', BROWSER, ' Version: ', BROWSER VERSION)

The new field will show the literal strings and dimension values as a single entry, such as:

Browser: Chrome Version: 68.0.3440.106
Browser: Safari Version: 11.0
Browser: Firefox Version: 61.0
...

Example 3. Create hyperlinks from your data

If your data contains URLs, you can create clickable links that appear in tables by using the HYPERLINK function.

For example, suppose you've got a custom SKU dimension associated with the URL to the relevant product description in the PAGE dimension. The following formula creates a clickable link for each record in your data:

HYPERLINK(URL, SKU )

If your data doesn't contain the complete URL, or you need to combine multiple fields to create the URL, use CONCAT to construct the link:

HYPERLINK(CONCAT("HTTPS://mydomain.com/", PARTIAL-URL, "?some-parameter"), SKU )

You can also use the HYPERLINK function to create clickable images in your tables.

Learn more about HYPERLINK.

Limits of calculated fields

Formulas in calculated fields can't mix unaggregated data (dimensions) and aggregated data (metrics).

For example, the following is not allowed:

CASE WHEN Country = "England" AND Conversions > 100 THEN ... // invalid formula
In this example, Conversions is an aggregated metric.

Was this helpful?

How can we improve it?
Search
Clear search
Close search
Google apps
Main menu
12882986245137364705
true