*length(${categories.full_path_name})*

# Analytics Calculations: Cell functions

josephmckeown
Moderator, Lightspeed Staff Posts:

**81**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:

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:

...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 June 2018

Joseph McKeown

Lightspeed Analytics and reporting consultant

Lightspeed HQ

Lightspeed Analytics and reporting consultant

Lightspeed HQ

#### Recent Discussions

#### Howdy, Stranger!

#### Popular Tags

- api 338
- retail 165
- eCom 139
- ecom api 98
- sales 77
- eCommerce 75
- integrations 69
- Lightspeed 64
- Lightspeed Retail 63
- Lightspeed Analytics 60
- Custom Field 46
- Lightspeed eCom 45
- product 45
- shipping 39
- support 38
- Reporting 35
- Product Development 34
- Payments 34
- customization 31
- apps 30
- receipts 29
- Lightspeed Payments 28
- sale 28
- customers 27
- workarounds 27