Analytics Calculations: Cell functions

josephmckeownjosephmckeown Posts: 37Moderator, Lightspeed Staff moderator
edited June 2018 in Reporting & Analytics
*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:
lower(${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.
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
Product Specialists, Post Sales
Lightspeed HQ
Sign In or Register to comment.