**if(**

*criteria*

**,**

*result if true*

**,**

*result if false*

**)**

josephmckeown
Moderator, Lightspeed Staff Posts: **80** moderator

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*

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.

Let's

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:

So in this case, we want our calculation to look like this:

So let's open the grey

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

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

Now let's define the rules if the Product is

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

but

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"

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

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.

Post edited by josephmckeown on June 2018

Joseph McKeown

Lightspeed Analytics and reporting consultant

Lightspeed HQ

Lightspeed Analytics and reporting consultant

Lightspeed HQ

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