Analytics Calculations: Simple Inventory Views

josephmckeownjosephmckeown Moderator, Lightspeed Staff Posts: 80 moderator
edited June 2018 in Reporting & Analytics
Keeping visibility over Inventory movement in Lightspeed can be tricky at times. What products are selling well? Should I keep ordering more of them? What if some are on Order right now, will they be sufficient to meet my projected need?

Reorder points tend to be based on three main measures:
  • How many products sold? (Sales Measure)
  • How many are currently in stock? (Item Metrics Measure)
  • How many are coming for stock? (Item Metrics Measure)
Sales reports tend not to feature the Item Metrics measures and dimensions. We can find them on the Inventory reports such as "Dusty". To prepare the report, let's open up the filters at the top of the page...

...and remove the dusty filters: "Item is Dusty" and "Quantity on Hand > 0"

We'll most likely get an error message around here, saying that there are too many rows. There are a few ways that we can change this...

...we could add an Item filter for Vendor, and just focus on one or two main Vendors. We could also add a filter for the Sale Line Completed Date. We can also increase our default row limit from 500 rows up to 5,000 rows

Now let's change the measures on this report. We can remove the measures of "Days Since Sold", "Days Since Received" and "Total Cost". Then we can add: Item Metrics> On Order> Quantity on Order, and Sale Line> Totals> Quantity Sold

So now, let's say we want to measure how much Inventory is either in store or coming for stock, we can determine this by adding the columns "Item Metrics Quantity on Hand" and "Item Metrics Coming for Stock". Let's go to the black "Data" bar, and towards the right hand side, click the grey "Calculations" button, and open up the Table Calculations editor...

To add the columns to our calculations, start typing the name of the column that you want to include. Let's start with Quantity On Hand...

...once I select "Quantity On Hand" it will show up in my calculation field as "${cl_item_facts.quantity_on_hand}" , to complete the addition, let's add the plus symbol "+" and then look for "Quantity on Order"

So, if I have completed the calculation properly, once I click "Save Table Calculations", I should get a new green column on my report titled "Calculation 1" with the totals of each Item's Inventory

Now let's say, of the products that sold, I still want to have at least half of the sold quantity in stock. We could click on the grey "Calculations" button again, and then select "Add Table Calculation", giving us a field for a new calculation...

...and then, let's look for "quantity sold", it will show up as "${cl_item_shop_sales.total_quantity}"...

...then to determine half of what was sold, we can divide this quantity by 2 by adding forward slash: "/", and then the number 2...

I may get some instances of decimals here, Looker has a ROUND function to round up to a certain number of decimals, but we'll look at that later.

So now that we know the available inventory level as well as the desired inventory level, we can add one more calculation to determine which products we need to re-order.

This is now a simple calculation: Available-Desired.
  • If we have more in stock then desired (difference is greater than zero; positive), we don't need to order any more.
  • If we have fewer in stock then desired (difference is less than zero; negative), we do need to order more
So let's subtract Calculation 2 (amount desired) from Calculation 1 (amount available), (we can do this by combining Calculations, I will show you how to do this in future posts. For now, we're going to keep it simple by referencing the Calculated fields)

Let's click on the grey "Calculations" button again, click on "Add Table Calculation" and open up space for Calculation 3...

Now, we'll look for "Calculation 1"...

...and then subtract "Calculation 2"...

So when I save it, I'll get a new field showing me the difference between what's in stock and what I want to have...

...and if I sort by this field, it will bring up all the inventory levels that I need to order!

*There are ways that we can use more calculations to make this report simpler to read, such as combining all desired calculated fields, reversing the negative for positive inventory ordering, and using IF statements to hide products that do not need to be re-ordered. We'll look at these in future posts

Calculations Home
Post edited by josephmckeown on
Joseph McKeown
Lightspeed Analytics and reporting consultant
Lightspeed HQ
Sign In or Register to comment.