Analytics Calculations

josephmckeownjosephmckeown Posts: 52Moderator, Lightspeed Staff moderator
edited 6:27PM 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 softwear 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, 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, Mean/Median/Mode, 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

Visualization tools

Analytics Limitations

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"

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 Daily Targets: Looking at how the values in Daily Targets are calculated

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 report on
Post edited by josephmckeown at
Joseph McKeown
Lightspeed Analytics and reporting consultant
Lightspeed HQ
Sign In or Register to comment.