You can create new metrics and dimensions in your data source using calculated fields. Calculated fields let you manipulate the information contained in your data source. For example, you can apply mathematical formulas, extract or transform text, and return data based on logical comparisons. You can use these new field in your reports by selecting them from the metric or dimension picker, just as you would any other field.In this article:
Watch a videoUse calculated fields
Create new calculated dimensions and metrics, which you can then use in your charts and controls
Create a calculated field
To create a calculated field:
- Edit your data source
- Click the blue plus at the top of the Field column:
- Enter a name for this field.
- Enter the formula for this new calculated field. Example:
- Click CREATE FIELD on the right.
How calculated fields work
Calculated fields appear as new fields in your data source, and you can use them in charts and controls. You can also refer to a calculated field in another calculated field. Calculated fields work by applying a formula to each row of data displayed by the charts or controls using that field. Calculated fields are aggregated in the same way as regular fields.
The following table lists some of the courses available in an online academy, along with the number of students enrolled.
|Science||Cold Fusion for Dummies||
|Veterinary||Dragon Agility Training||
|Music||Build Your Own Bagpipes||
|Veterinary||Teach Your Gerbil to Code||
|Veterinary||Koala Racing: Rules and Regulations||
|Music||Heavy Metal Ukulele||
To summarize the courses and students enrolled per category, you can create a simple calculated field to count the number of courses using the COUNT function:
A table in Data Studio with this data might look like this:
|Category||Number of Courses||Enrolled|
In this example, Data Studio has aggregated (summarized) both the Enrollment field and Number of Courses calculated field per category.
Calculated field syntax
Formulas in calculated fields consist of the following:
Functions let you create formulas that use math, text handling, date transformation, logical comparison, and other operations. A formula can use multiple functions, along with operators, to create sophisticated calculated fields. Function names are case-insensitive.
A function argument tells the function what to act upon. Most functions expect one or more field-expressions as arguments. A field-expression is some text that evaluates to a field name in your data source. Typically, you will just use the actual name as it appears in the data source.
Some functions accept literal values, such as text or numbers. Be sure to enclose text in single or double quotes.
Still other functions expect arguments that specify date formats or geographic codes. Expand the sections below for more information on date and geo formats.
All date functions use UTC as their timezone.
Supported date function input formats
Date functions can take an optional
input-date-format string which formats the function output:
- '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
In the Geo related functions, GeoInputFormat is one of the following strings:
The valid geo codes are defined here:
GeoInputFormat is optional if the field expression is a derived column already containing semantic geo information, otherwise it is required.
CASE WHEN Favorite Color = "blue" THEN "Me, too!" ELSE "boo" END
MAX(Wait Time) + 1
CONCAT('Browser:', Browser, 'Version:', Version-number)
Numeric calculated fields support the following operators:
- Addition: +
- Subtraction: -
- Division: /
- Multiplication: *
You can construct formulas using any of the operators above in combination with static numeric values and numeric fields from your data source. Use parentheses to force calculation order.
Price + Tax
(Students Enrolled / Students Passed) * 100
Return data type
All functions return results in a specific data type. Typically, this depends on the function itself: for example, math and aggregation functions return numbers, text functions return text. Most date functions return a number, except for the TODATE function, which returns text.
You can change or refine the data type of your calculated fields using the Type drop-down menu in the data source editor.
A field's aggregation determines how it will be 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.
Am I creating a dimension or metric?
The type of field you create—a new dimension or new metric—depends on the end result of your formula. If the result is a number of some kind, the new field will be a metric. If the end result is a string (text) or a date, the new field will be a dimension.
Edit calculated fields
After you've created a calculated field, click the fx chip to edit the formula.
Troubleshooting calculated fields
If Data Studio finds an error with a calculated field, it will display a message and will not create the new field. Here are a few reasons why your formula might be rejected:
- Trying to use an operator on a non-supported type.
- Mismatched parentheses.
- Incorrect number or type of arguments.
- Attempting to aggregate an already aggregated field. For example, using the AVG() function on a field that is already summed or averaged.