Hi. How can we help?

String functions

Analytics provides lots of functions that perform different mathematical, logical, and table-based calculations. You can find a more in-depth catalog of available functions on the list of Looker functions and operators page.

To make it easier, we could categorize these in five different ways: 

This article covers seven of the string, or text-based, functions.

Concat

Concat is used to concatenate, or to pull content from two or more sources and gather into one. To use concat, separate each part of the final source by using commas.

In this example, there is a single cell that begins with the sales employee's name, followed by a space, and then followed by that employee's total hours worked:

concat_calculation.png

This final calculation is written as:

concat(${employees.full_name_2}," ",${cl_employee_hours_dates.total_hours_worked})

When formatted correctly, it looks like this: 

concat_results.png

Length

Length is used to determine how many characters including spaces are in a given cell.

In this example, I'm interested in how long each category title is. We only look at one field or calculation

length_calculation.png

The final calculation is written as:

length(${categories.full_path_name})

When formatted correctly, it looks like this: 

length_result.png

Lower/Upper

Lower/Upper returns all of a cell’s content exclusively in lower-case or upper-case letters respectively

In this example, returning all email addresses in both lower and upper case letters. Only one argument is required:

lower_and_upper_calculations.png

In this case, my calculations look like this:

lower(${contact_emails.address})

upper(${contact_emails.address})

If the calculation is entered correctly, it looks like this:

lower_and_upper_result.png

Position

Position returns the number where a certain string of text starts in a certain cell.

The first argument is what cell I am searching for, and the second is what content I am looking for. For example, let's say I want to find the categories that contain the word Mountain.

I prepare my calculation like this:

position_calculation.png

The calculation:

position(${categories.full_path_name},"Mountain")

If the calculation is saved properly:

position_results.png

Replace

Replace replaces all content that matches certain criteria with defined replacement content.

In this example, I want to replace the word "Bikes" in my product categories with the word "Chocolate". My first argument is the source of content (the Product Category), followed by a comma, followed by what to replace (case sensitive), followed by a comma, followed by what to replace it with:

replace_calculation.png

In this example, my calculation looks like this:

replace(${categories.full_path_name},"BIKES","CHOCOLATE")

If formatted properly, it looks like this:

replace_result.png

In another example, I could pull what I want to look at from two sources of content. Say, for example, I want to find if the brand of a product can be found in its description. If this is the case, I could prepare a replace function to look at the description, to replace the manufacture with empty content: "")

replace_calculation2.png

My final calculation looks like this:

replace(${items.description},${manufacturers.name},"")

If I've done my calculation properly:

replace_result2.png

Substring

Substring looks at a part of specific content in a cell.

To prepare the calculation, the first argument we make is which field we want to look at, followed by a comma.

The second argument is a number: the character in the cell we want to begin with.

The third and final argument is also a number, how many characters we want to return in our results.

Here are a few of these, just looking at categories:

substring_calculations.png

My calculations all look like this, but with different numbers:

substring(${categories.full_path_name},1,5)

If everything is correct:

substring_result.png

Was this article helpful?

0 out of 0 found this helpful