Using calculated fields in schema

You can add calculated fields in your schema and use the formula property in schema to define the calculations.

You can view the list of functions that can be used in calculated fields. You should not use any of these function names as name for your fields. You can also view specific references with examples for the following functions: CASE, CAST, TODATE, HYPERLINK, IMAGE, REGEXP_EXTRACT, REGEXP_MATCH, and REGEXP_REPLACE.

semanticType for calculated fields

You can optionally set the semanticType for your calculated field in the schema.

  • If the output of your calculated field is a number, you can set semanticType to NUMBER or PERCENT.
  • If the output is a string that matches the format of any DATE_AND_TIME or GEO semanticTypes, you can set the semanticType to the corresponding enum (e.g. if the output for the calculated field if "20170317"(string) then you can set semanticType to YEAR_MONTH_DAY)
  • For all other string outputs, the semanticType should be set to TEXT.

If you do not set the semanticType in the schema, Data Studio will use semantic detection process to identify semanticType.

Examples of calculated fields in schema

The following example schema contains two calculated fields. In the formula, you have to use the name for any other field you are referring to.

{
  "schema": [
    {
      "name": "t_celsius",
      "label": "Temperature (Celsius)",
      "description": "Temperature in Celsius",
      "dataType": "NUMBER",
      "semantics": {
        "conceptType": "METRIC",
        "semanticGroup": "NUMERIC",
        "semanticType": "NUMBER",
        "isReaggregatable": false
      }
    },
    {
      "name": "t_fahrenheit",
      "label": "Temperature (Fahrenheit)",
      "description": "Temperature in Fahrenheit",
      "dataType": "NUMBER",
      "formula": "t_celsius/5*9+32"
      "semantics": {
        "conceptType": "METRIC",
        "semanticGroup": "NUMERIC",
        "semanticType": "NUMBER",
        "isReaggregatable": false
      }
    },
    {
      "name": "weather",
      "label": "Weather (based on temp)",
      "description": "Temperature in Fahrenheit",
      "dataType": "STRING",
      "formula": "CASE WHEN t_celsius<0 THEN \"Freezing\" WHEN t_celsius>40 \"Too warm\" ELSE \"Not bad\" END",
      "semantics": {
        "conceptType": "DIMENSION",
        "semanticType": "TEXT"
      }
    }
  ]
}