Search
Clear search
Close search
Google apps
Main menu

About calculated fields

Transform, categorize, and do math with your data.

Calculated fields let you create new metrics and dimensions in your data source that perform arithmetic, apply mathematical formulae, extract or transform text, or return new information based on logical comparisons. You can use the new dimensions and metrics in charts and controls, just like regular fields.

You must have edit rights to the data source to create calculated fields.
In this article:

Watch a video

This video explains the basics of creating calculated fields, including using simple math, CASE statements, and the REGEXP_MATCH function.

Use calculated fields

How calculated fields work

Calculated fields appear as new fields in your data source. If the result of the formula is an aggregated number of some kind, the new field is a metric. If the end result is a string (text), a date, or an unaggregated number, the new field is a dimension.

You can use a calculated field in charts and controls, just like other fields. You can also include a calculated field in another calculated field.

A calculated field adds a new column to your data. How this new data displays depends on how you use it in your reports. The example in the screenshot above creates a new Total field that multiplies the quantity sold field (Qty Sold) by the unit price field (Price). When used in a table, the calculated Total field shows the product of that multiplication for each row.

Example table showing total price calculated field.

Example of using a calculated field in a table.

When used in scorecard, the Total field displays the sum of the products for all rows in which quantity is multiplied by price.

Example of using a calculated field in a scorecard.

Just like normal fields, calculated fields are subject to any filters or date ranges in effect in the report.

What you can do with calculated fields

Calculated fields let you do the following kinds of things.

Do basic math with numeric fields

You can do simple arithmetic calculations using the normal operators:

  • Addition: +
  • Subtraction: -
  • Division: /
  • Multiplication: *

You can construct calculated field using any of the operators above in combination with static numeric values and unaggregated numeric fields from your data source. Use parentheses to force calculation order.

Examples

Users/New Users

(Price * Quantity) * .085

Apply functions to calculated fields

Functions let you create more advanced calculated fields. Data Studio supports the following kinds of functions:

  • Aggregation functions perform calculations over multiple rows of your data. Examples include SUM(), AVG(), MIN(), MAX().
  • Arithmetic functions apply advanced mathematical calculations to your data. Examples include LOG(), POWER(), ROUND().
  • Date functions let you manipulate and transform time data. Examples include TODATE(), DATE_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().

Function arguments

All functions expect input, called an argument, that tells the function what 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.
Be sure to enclose literal text in single or double quotes.

Date functions assume UTC as their timezone. Date functions can optionally take either an input or output (or both) format string.

Date formats

  • 'BASIC' - Supported format YYYY/MM/DD-HH:MM:SS
  • 'DEFAULT_DASH' - Supported format YYYY-MM-DD [HH:MM:SS[.uuuuuu]]
  • 'DEFAULT_SLASH' - Supported format YYYY/MM/DD [HH:MM:SS[.uuuuuu]]
  • 'DEFAULT_DECIMAL' - Supported format YYYYMMDD [HH:MM:SS[.uuuuuu]]
  • 'RFC_1123' - Sample format: Sat, 24 May 2008 20:09:47 GMT
  • 'RFC_3339' - Sample format: 2008-05-24T20:09:47Z
  • 'SECONDS' - Seconds since epoch
  • 'MILLIS' - Millis since epoch
  • 'MICROS' - Micros since epoch
  • 'NANOS' - Nanos since epoch
  • 'JULIAN_DATE' - Days since epoch
  • 'DECIMAL_DATE' - Same as 'DEFAULT_DECIMAL'
  • Any valid strptime format as string

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.

Use branching logic in calculated fields

CASE statements let you perform branching "if/then/else" style logic in your calculated fields. For example, the following CASE formula categorizes countries into regions:

CASE 
    WHEN Country IN ("USA","Canada","Mexico") THEN "North America" 
    WHEN Country IN ("England","France") THEN "Europe" 
    ELSE "Other" 
END

Learn more about CASE.

Return data type

Calculated fields return results in a specific data type. For example, if your calculated field uses arithmetic or aggregation functions, the calculated field's return type is Number. If the calculated field uses a text function, the return type is Text.

Most date functions return a Number, except for the TODATE function, which returns a Date type.

You can change the data type of your calculated fields using the Type drop-down menu in the data source editor.

Aggregation

A field's aggregation determines how it is summarized in your reports. Data Studio tries to assign the most appropriate aggregation to a calculated field. For certain functions, the aggregation is implicit: e.g., MEDIAN, AVG, SUM. In this case, the aggregation is set to Auto and cannot be changed. For other functions, you can specify the aggregation type using the Aggregation drop-down menu.

Data type and aggregation drop-down menus

Troubleshoot calculated fields

If there is an error in a calculated field formula, you'll see a warning message, and you won't be able to save the new field. Here are a few reasons why a formula might be rejected:

Reason Possible Solutions

Invalid field name.

A valid field name automatically appears as a green or blue chip in the formula editor.

Check the spelling and any special characters in the field name.

Invalid function name.

A valid function automatically appears in uppercase green letters in the formula editor.

Check the spelling of the function name.

Missing quotes.

Literal strings must be quoted with matched pairs of single or double quotes.

Make sure all string literals are properly quoted.

Mismatched parentheses.

When nesting functions, it's easy to miss a closing parentheses.

Make sure you have the same number of opening parentheses as closing parentheses, and that they are in the right spots.

Function argument type mismatch.

Functions expect arguments to be of a particular type. If there's a mismatch, it can cause an error. For example, trying to use a Date function on a Text dimension.

Choose the correct input values. You can possibly use CAST() to change the input value type.

Re-aggregating metrics is not supported.

Aggregation functions can't be applied to already aggregated data. This includes most metrics found in Google Analytics, and AdWords. For example, Sessions is already summed in your data set; the formula SUM(Sessions) will produce an error.

This limitation comes from the underlying data set. Any solution will involve changing how the data is appears there, if possible.

An expression can have either metrics, or dimensions, but not both.

You can't mix dimensions and metrics in function arguments.

Make sure you are using the appropriate function for your data. For example, use CONCAT() to append text to a Text field, instead of using +.

 

Formula examples

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

MAX(Wait Time) + 1

CONCAT('Browser:', Browser, 'Version:', Version-number)

DAY(hit-time, 'MILLIS')

TOCITY(city-info, 'CRITERIA-ID')

Was this article helpful?
How can we improve it?
What's new in Data Studio

Learn about new features and recent changes.