Analytics Calculations: Cell functions
josephmckeown
Posts: 40Moderator, Lightspeed Staff moderator
*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,
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:
and if I've done my work properly, looks like:
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:
...and if I've done my work properly, it looks like
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...
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:
the calculation reads:
and if I have saved it properly...
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:
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:
and if I've done my calculation properly...
Look at a part of specific content in a cell.
My calculations all look kind of like this, but with different numbers:
and when I save it, if I have done my work properly...
Calculations Home
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:
lower(${contact_emails.address})
upper(${contact_emails.address})
upper(${contact_emails.address})
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:
the calculation reads:
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.
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
Lightspeed Analytics and reporting consultant
Lightspeed HQ