Analytics Calculations: Simple Inventory Views II (IF Statements 1.2)

josephmckeownjosephmckeown Moderator, Lightspeed Staff Posts: 59 moderator
edited June 2018 in Reporting & Analytics
IF functions can look at dimensions as well as measures.

Let's say that when re-ordering products, Vendors need more time to fulfil orders from certain Categories than with others.

So we may want to order more relative inventory from these Categories than our typical Categories.

Going back to our first Simple Inventory views report, let's also add "Top Level Category" as a dimension, and run the report.

To jump-start this report, run "Dusty", remove the filters of "Is Dusty" and "Quantity is greater than zero". Add a filter of Sale> Completed Date "days since sold" is equal to or less than 60. You may wish to add filters for specific Vendors to ensure that your row count is complete. You may additionally wish to change the default row limit from 500 to 5,000
Let's remove the measures "Days Since Sold", "Days Since Received", "Total Cost", and "Total Inventory" and let's add the measures Sale Lines> Totals> Quantity Sold
For the purposes of this exercise, we can forego Quantity on Order and Quantity in Stock


Our starter report should look something a little like this...


Now we have a few main Categories that show up in our results here. Let's suppose that the "Components" need to be re-ordered at a higher volume than the other Categories. If we need to replenish the stock of standard products at 50% of the quantity sold, maybe we need to replenish the stock of "Component" products at 75% of the quantity sold.

Remember that our IF statement needs to take the following shape:
if( criteria , result if true , result if false )

So in this case, we want our calculation to look like this:
if( Product Category is "Components" , re-order 75% , re-order 50% )

So let's open the grey "Calculations" button and start our if function...


Part One; IF Criteria

We want to find instances where the top-level category is "Components", so I'm going to type "if(" and then start looking for the word "Category"...

...I see "Top Level Category" here, so I'm going to select it, enter the equals sign: "=" , and then look for "Components"...

...now Analytics is giving me an error message, it looks like it doesn't recognize the word "Components". What I need to do then, is add quotations to the beginning and end of the word "Components" so that Analytics understands it's a word that I'm looking for...

...and we add the comma to proceed to value if true...


Part Two; IF True

Now if the Product is in the "Components" Category, we want to re-order 75% of the quantity sold. Remember that this will look like "quantity sold * 0.75"

...followed by a comma...

Part One; IF False

Now let's define the rules if the Product is not in the Category of "Components", as being 50% of what was sold. This will look like "quantity sold * 0.5

So now if I save my table calculations, I should see a green column called "Calculation 1"

but wait a minute! My calculation is returning 50% of everything that was sold, whether it was in the Category of "Components" or not. I must have made an error.

Let's go back to the grey "Calculations" button. Can you guess what the error is?


Well, if you guessed Case-Sensitivity, you're right. The Categories are "COMPONENTS", (completely spelled in capital letters) not "Components"

Let's try that again.


Now that looks better


And once again, I can go back to my "Calculations" button to rename "Calculation 1" to something more meaningful like "Reorder Amount"




An important note on data integrity

Now, when we start using words as the basis of logic in Analytics, keep in mind that your results will follow the integrity of data as it is entered in your Lightspeed account. If any Categories have erroneously been created more than once with slight variance, if products have not been assigned to Categories, if Categories have been changed and a full data re-sync has not been requested through Support, then your results may differ from what you expect.

There are some ways that you can correct for these using more in-depth calculations. Calculations can used to return the lower-case version of words, they can look at a second set of criteria if a first one is not met, (eg, if the Manufacturer has not been entered constantly, perhaps the Vendor has) they can identify instances of criteria not being expected, and highlight these results. These can be effective tools, but they can only correct for anticipated errors.

The more consistent your team is with clean data, the less space for error your calculations will have, and the more powerful your Analytics calculations will be.


We could turbo charge this report still by adding calculations identifying what is coming for stock, what is currently in stock, as well as rounding out decimals in calculated results.



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