**A note on "greater than/ less than" logic**

# Analytics Calculations: Simple Commission II (IF Statements 1.1)

josephmckeown
Moderator, Lightspeed Staff Posts:

**78**moderator
IF functions are calculations that produce different results when certain criteria are met. They can look at measures, dimensions, calculations, as well as other data such as the current date.

Simple IF functions are always constructed the same way in Analytics:

So let's try a simple version of this, perhaps the employee commission model that I looked at in an earlier discussion.

Let's say that at our store, employees have to hit a certain dollar amount of sales

To illustrate, let's find a midway point in the Sales Totals, I can see from my demo data, that some of the employees are well over $20,000 but others have not yet got to $2,000...

...so for now, I will set my sales target as $5,000. My employees will need to hit $5,000 in total sales before being considered for commission.

Let's think about what our calculation should look like. Going back to the model above...

Our argument should look like this:

Our initial approach to this may be...

but, this means that employees who hit $5,000 will not be eligible, because $5,000 is not greater than $5,000. You could fix this though by changing the statement to be:

As we'll see in subsequent discussions, there may be many ways to produce the same results)

Now let's click on the grey

The

...then the

...then 5000 followed, by a comma

So now, our criteria is complete! Next, we want to add the value

In this example, if the employee has

Finally, we need to define the value if not true. If the employees sales are not less than $5,000, they get commission. Let's say that their commission is 4% of the total sale. Remember that we define this as

to complete the IF function, add the final closing bracket symbol: ")"

So now, if we have prepared the calculation correctly, when we save the table calculations, we will see a new green calculation column on our report called

...and if we open the grey

...and there is our first IF function!

Spoiler alert, our final calculation in this model is:

if(${cl_employee_sales_dates.sum_total_no_tax}<5000,0,${cl_employee_sales_dates.sum_total_no_tax}*0.04)

Simple IF functions are always constructed the same way in Analytics:

*(including the punctuation)*

**if(**

*criteria*

**,**

*result if true*

**,**

*result if false*

**)**

Let's say that at our store, employees have to hit a certain dollar amount of sales

*before*they are considered eligible for commission. Let's open up the Employee Team Performance report. Let's remove the measures we don't need:*"Sales by Hour"*.*"Profit by Hour"*,*"Total Hours"*, and*"Basket Size"*,To illustrate, let's find a midway point in the Sales Totals, I can see from my demo data, that some of the employees are well over $20,000 but others have not yet got to $2,000...

...so for now, I will set my sales target as $5,000. My employees will need to hit $5,000 in total sales before being considered for commission.

Let's think about what our calculation should look like. Going back to the model above...

**if(**

*criteria*

**,**

*result if true*

**,**

*result if false*

**)**

Our argument should look like this:

**if(**

*total sales is less than $5,000*

**,**

*no commission*

**,**

*commission*

**)**

Our initial approach to this may be...

but, this means that employees who hit $5,000 will not be eligible, because $5,000 is not greater than $5,000. You could fix this though by changing the statement to be:

As we'll see in subsequent discussions, there may be many ways to produce the same results)

*if (total sales is greater than $5,000, commission, no commission)*

*if(total sales is greater than $4,999.99, commission, no commission)*

**Part One; IF Criteria**Now let's click on the grey

*"calculations"*button to open up our Table Calculations, and let's start our IF function...

The

*first*part of our if function is,

*if total sales is less than $5,000*..., so we'll look for "Total sales"...

...then the

*"less than"*symbol: <

...then 5000 followed, by a comma

**Part Two; IF True**So now, our criteria is complete! Next, we want to add the value

*if true*.

In this example, if the employee has

*less than*$5,000 worth of sales, there is no commission. This means, our value

*if true*is, zero. We follow this with a comma...

**Part Three; IF False**Finally, we need to define the value if not true. If the employees sales are not less than $5,000, they get commission. Let's say that their commission is 4% of the total sale. Remember that we define this as

*total sales * 0.04*

to complete the IF function, add the final closing bracket symbol: ")"

So now, if we have prepared the calculation correctly, when we save the table calculations, we will see a new green calculation column on our report called

*"Calculation 1"*

...and if we open the grey

*"calculations"*button once more, we can change the number formatting from default to U.S. dollar, as well as change the title of the column to "4% Commission"

...and there is our first IF function!

**To turbo-charge this report, we could add a column calculating how close employees are to hitting target by subtracting actual sales from sales target. We could also divide their sales over the target to determine how close they are percentage wise to hitting target. We could define multiple tiers of targets, (if they hit $5,000, they get 2%, if they hit $10,000, they get 4%), and so on...*

Spoiler alert, our final calculation in this model is:

if(${cl_employee_sales_dates.sum_total_no_tax}<5000,0,${cl_employee_sales_dates.sum_total_no_tax}*0.04)

*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 278
- retail 130
- eCom 117
- ecom api 73
- eCommerce 71
- sales 64
- Lightspeed 58
- Lightspeed Analytics 58
- integrations 53
- Custom Field 35
- shipping 34
- product 33
- Lightspeed eCom 32
- support 32
- Product Development 30
- apps 30
- customization 26
- Payments 25
- theme 23
- Lightspeed Payments 23
- themes 22
- Lightspeed Retail 22
- customers 21
- receipts 20
- producten 20