Analytics Calculations

josephmckeownjosephmckeown Moderator, Lightspeed Staff Posts: 87 moderator
edited June 17 in Reporting & Analytics
Calculations are one of the most powerful tools you have when it comes to building personalized reports in Lightspeed Analytics:
  • They can help with custom sorting: highlighting areas in your Lightspeed activity that you want to focus on.
  • They can help prepare custom graphics: displaying activity in Lightspeed in a quick and meaningful way.
  • They can help do math and logical problems for you.
  • They can help prepare data for more in-depth analysis, exporting to spreadsheet software or other analytic programs.
The language of calculations starts very simply, and can get quite sophisticated, depending upon your comfort level. (More advanced Lightspeed users may be interested in learning about Looker Syntax Reference)

Updated! There are some important new changes that may improve upon the articles below.
Here are the latest changes made to the reporting views: Recent Updates to Analytics
take a look at recent
changes to the Calculations tools.

Let's start with some simple examples...

Average Sales
Simple Inventory Views
Simple Commission
Simple Commission II (IF Statements 1.1)
Simple Inventory Views II (IF Statements 1.2)

Simple Commission III (Multiple IF Statements 2.1)
Simple Commission III (Multiple IF Statements 2.2)
Customer Contact (Multiple IF Statements 2.3)

Then when you're ready for some more complicated tools:
Compound Calculations, some best practices
Cell-based Functions: Concat, Length, Lower/Upper, Position, Replace, Substring
Date-based Functions: Add_Days, Date, Diff_Days, Extract Day, Now, To_Date, Trunc_Days
Logic Functions: And, is_null, No, Not, Or, Row, Yes
Math Functions: Large, Max, Mean/Median/Mode, Min, Round, Runningtotal, Small, Sum
Table Functions: Count, Count Distinct, Index, Match, Offset, Offset List

About Pivoting
Pivot-based Functions: Lookup, Pivot Column, Pivot Index, Pivot Offset, Pivot Offset List, Pivot Where

Visualization tools

Data Sets
Analytics Data Sets: A basic overview of the data you can expect to find in different Analytics reports.

Analytics Limitations

Pre-Built Table Calculations: A new tool that makes preparing calculations much easier

Compound Calculations:
Percentage of Total
Multi-Tiered Sorting: Sorting by two numbers or more
Multi-Tiered Sorting 2: Sorting numbers and non-numeric values
Match-Index 1: Top Categories by Store
Match-Index 2: Setting Financial Year by Month
Match-Index 3: Finding Duplicates
Totals by Varying Timeframes: Sales this month, last month, year-to-date and this month last year
Totals by Varying Timeframes 2: Sales this month to date, last month-to-date, this month last year to date
Dynamic Reorder Points: Using "Days to Sell Out" as a way of projecting demand 
Group and Summarize results: Using Offset List, Match, and Index to summarize and gather results
Last Date Sold: Turning "days since sold" into "date last sold"
Profit Potential: Calculating at potential for profit and margin in existing inventory
Recreating Discounts: Reporting on the dollar value of discounts applied to sales

Preparing Data for Further Analysis
To First Subcategory: Trimming full categories to just first subcategory
Matching Payment Methods to Categories: Preparing two reports in Analytics for conceptualizing in Excel
Taxes by State or Zip: Highlighting tax collected by state or zip code

Importing Data Back into Lightspeed
Define Product Category: Finding uncategorized Products and getting ready to batch-update them
Import Inventory onto a PO:  Creating a new PO from your Dynamic Reorder Points report
Tag Dusty Products in Retail: Use Calculations to create tags for Product actions in Retail

Understanding the Goals Dashboard: Looking at how the Goals dashboard is constructed
Understanding Costs on Inventory Dashboard: Seeing how numbers are determined, and how to act next.
Understanding Daily Targets: Looking at how the values in Daily Targets are calculated
Understanding Customer Lifetime Value: Illustrating significance of different measures and filters on report
Understanding Null Dimensions: How to read and action when reporting results are not defined
Understanding Totals and Tags: Knowing what numbers mean when we use Tags as a Dimension
Understanding Average Basket Value: Understanding how the values and calculated values align
Deleting a Report Subscription: The steps to stopping scheduled reports from being delivered
Historical Asset Inventory: Understanding the Analytics Historical Asset Inventory report
Some similar but different Sales measures: # of Items, # of Sales, # of Sales Lines, Quantity Sold

Channel is the new Is Ecom: Update to clarify Sales channel
Using Matches Advanced as a Sale Date Filter: Using simple commands to report on different timeframes
Item Metrics: Tag Collection: A new filter for building reports using Tags
New Omni eCom Reports: An introduction to the eCom Omni Reports in Analytics
New Sales Tax Data: Some new data points being added to the Tax Collected report

Custom Dimensions

Introduction to Custom Dimensions: getting ready to use Custom Dimensions

Relative Date of Year: Comparing this year's YTD performance to the same time in years past
Same Month to Date as last year: Comparing this MTD to the same period in years past
Redefining Size Convention: Gathering results from raw Sizes into meaningful groupings, sorting by size convention
Item or Matrix: Gathering Products together around Matrix groupings if applicable
Multi-Date Snapshot: Showing Qty Sold of product across different groupings of days
Change Week to Monday through Sunday: If you need to redefine how weeks are reported
Sales except with X tag: isolating sales totals for products that do not include a certain tag

Custom Measures

Introduction to Custom Measures: getting ready to use Custom Measures

Searching for X Tag on Products: identifying Products with and without a certain tag
Duplicate Customers: Identifying names or email addresses that are associated with multiple customers
Dusty Everywhere: Counting and listing the stores in which Items are considered dusty
Out of Stock Everywhere: Counting stores in which an Item is out of stock

See also:
Reports in tandem with our Lightspeed Blog
Post edited by josephmckeown on
Joseph McKeown
Lightspeed Analytics and reporting consultant
Lightspeed HQ
Sign In or Register to comment.