# Analytics Calculations: Simple Inventory Views

josephmckeown
Moderator, Lightspeed Staff Posts:

**80**moderator
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:

...and remove the dusty filters:

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

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

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

So, if I have completed the calculation properly, once I click

Now let's say, of the products that sold, I

...and then, let's look for

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

Let's click on the grey

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!

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)*

*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 InventoryNow 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

*(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 June 2018

Joseph McKeown

Lightspeed Analytics and reporting consultant

Lightspeed HQ

Lightspeed Analytics and reporting consultant

Lightspeed HQ

Tags:

#### Recent Discussions

#### Howdy, Stranger!

#### Popular Tags

- api 330
- retail 163
- eCom 137
- ecom api 92
- sales 76
- eCommerce 72
- integrations 65
- Lightspeed 63
- Lightspeed Retail 61
- Lightspeed Analytics 60
- Custom Field 45
- product 43
- Lightspeed eCom 40
- shipping 39
- support 36
- Payments 34
- Product Development 33
- customization 31
- Reporting 30
- apps 30
- sale 28
- Lightspeed Payments 28
- workarounds 27
- receipts 26
- customers 25