# Analytics Calculations: Cell functions

Moderator, Lightspeed Staff Posts: 80 moderator
edited June 2018
*Note, from this part of the Discussions, we will assume that you're comfortable with starting Calculations. Changing dimensions and measures, changing filters, and some of the basic starter calculations: addition, subtraction, division, multiplication, greater than, less than, equal to, single-criteria IF functions, and multiple criteria IF functions.

If you're in the early stages of your Analytics mastery, you may want to take a quick look at the related Discussions:

Multiplying by percentages in Analytics

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

To make it easier, we could categorize these in five different ways: Cell functions, Date functions, Logic functions, Math functions, and Table functions.

In this article, we'll be looking at seven of the Cell based functions:
Concat, Length, Lower/Upper, Position, Replace, Substring,

# Concat

Used to Concatenate, pull content from two or more sources and gather into one. To use, separate each part of the final source by using commas.

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

This final calculation is written as:

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

and if I've done my work properly, looks like:

# Length

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

The final calculation is written as:
length(\${categories.full_path_name})

...and if I've done my work properly, it looks like

# Lower/Upper

Returns cell 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...

In this case, my calculations look like:

and if I've done my calculation correctly...

# Position

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

The first argument is what cell I am searching, 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(\${categories.full_path_name},"Mountain")

and if I have saved it properly...

# Replace

Replaces all content that matches certain criteria with a 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.

In this example, my calculation looks like this:
replace(\${categories.full_path_name},"BIKES","CHOCOLATE")

and if I've done my calculation properly...

In another example, I could pull what I want to look from 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, "")

my final calculation looks like:
replace(\${items.description},\${manufacturers.name},"")

and if I've done my calculation properly...

# Substring

Look 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.
Let's do a few of these, just looking at Categories for now...

My calculations all look kind of like this, but with different numbers:
substring(\${categories.full_path_name},1,5)

and when I save it, if I have done my work properly...

Calculations Home
Post edited by josephmckeown on
Joseph McKeown
Lightspeed Analytics and reporting consultant
Lightspeed HQ