[Looker Studio] How to Use the CAST Function and Practical Examples | Calculated Fields

In this article, we provide a clear explanation of how to use the CAST function in Looker Studio, along with specific examples of its application. Let's utilize the functions available in Looker Studio to process and visualize data.

The CAST function is a function that converts the data type of a field to another data type. We will explain specific examples using data from a spreadsheet loaded into Looker Studio. We will also introduce methods to change data types without using the CAST function.

What are Calculated Fields in Looker Studio?

Calculated Fields in Looker Studio are a handy feature that allows you to create custom fields by using operators (such as addition, subtraction, multiplication, division), functions, and regular expressions based on existing items for use in reports.

Also, for basic usage of Looker Studio, please refer to the "How to Use Looker Studio" guide. Looker Studio is a very convenient tool that is free to use and allows for the creation of easy-to-understand reports by connecting to various data sources, so let's make active use of it.

What is the CAST Function?

The CAST function is a function that can convert the data type of one field to another data type. For example, it can convert a number to a string, a date to a string, or vice versa. Data types include strings, numbers, dates, and datetime, and they define how the data in that field is handled. For instance, if there is data "123" in a field named "sales," if the data type is numeric, it can be added or subtracted, but if it is a string, it cannot be calculated as a number.

Syntax

The syntax of the CAST function is as follows:

CAST("field you want to convert data type" AS data_type)

Specify the field you want to convert in "field you want to convert data type".
For the data type, specify types such as TEXT or NUMBER.

About Data Types Usable in Looker Studio

The data types that can be converted are determined as shown in the table below.

According to this table, numerical data cannot be changed to dates, and vice versa. 

How to Use the CAST Function

 To use the CAST function, you need to create a calculated field in Looker Studio. There are two types of calculated fields: data source calculated fields and chart-specific calculated fields. This article will focus on creating a data source calculated field, but for more detailed differences between the two, please see the differences between data source calculated fields and chart-specific calculated fields.

Since the data from GA4 using connectors is already well-structured, this time I will present a use case where we structure the data types using custom data created in a spreadsheet as sample data.

Use Case: Changing data types from string to date in Looker Studio

We will create a report in Looker Studio using a spreadsheet like the one below as the original data.

 (Quote:Google sheet)

The dates in column A are intended to be treated as dates when creating reports in Looker Studio, but if the data is imported into Looker Studio as it is, it will become text type as shown below.

 (Quote:Looker Studio) 

If the report is created as is, the date will be treated as text, which means you won't be able to perform actions specific to date data, such as filtering by period. Therefore, we will convert it to a date type.

First, we will create a calculated field as follows.

① Field Name: Please enter any field name.

② Formula: CAST(date as date)

convert the date data field into a date type data type.

③ Save: Once you have completed entering the information, click save.

 A field named 'cast_date' has been created as follows. At this point, it can be confirmed that the data type has changed to a date.

 (Quote:Looker Studio)

Next, since the field we created earlier is now available for use in the report, we will add it to the table. Add the date dimension as cast_date and the sales metric as follows. Also, specify the cast_date in the period dimension.  

 (Quote:Looker Studio)

 We were able to create a new field named cast_date and treat it as a date type in this way.

Methods to Change Data Types Without Using the CAST Function

It is possible to change data types without using the CAST function. Let's explain the specific method. First, open the target data source, then click the arrow on the right side of the type, as shown in the image below. 

When the candidates for the type to change are displayed, select the type you want to change. This time, since we want to change it to a date type, we will select 'date'.

We have been able to change the data type.  

Relevant Looker Studio Official Documentation

Looker Studio Official Help : About calculated fields

Looker Studio Official Help : Function list