# Analytics Calculations: Simple Commission III (Multiple IF Statements 2.1)

josephmckeown
Moderator, Lightspeed Staff Posts:

**59**moderator**Note, in this Discussion, we will assume that you're now comfortable with a few points of Calculations. If you're in the early stages of your Analytics mastery, you may want to take a quick look at the related Discussions:*

Multiplying by percentages in Analytics

Using IF functions

*Part 1*

IF functions are not limited to two criteria. You can add arguments to test for multiple criteria to help with triage.

Remember from our first IF Discussion that for true or false criteria, the formula looks like...

**if(**

*criteria*

**,**

*result if true*

**,**

*result if false*

**)**

For

*multiple*criteria, the formula follows a pattern like this...

**if(**

*Criteria 1*

**,**

*result if Criteria 1 true*

**,**

**if(**

*Criteria 2*

**,**

*result if Criteria 2 true*

*, result if both Criteria 1 and Criteria 2 are false*

**))**

An important point in using IF statements, if the first set of criteria is met, the calculation will return the expected results and

*not*look for the second set of criteria met.

So let's say, like the example from our previous commission report, we have commission tiers that an employee may hit, depending on their sales:

If an employee hits $5,000 worth of sales, they get a 2% commission, if they hit $10,000 worth of sales, they get 5% commission, if they do not hit $5,000, they do not get any commission.

*A note on the order of arguments*We need to take a minute to think about the best order of arguments, so that the rules are applied as expected.

This is a

**way of preparing this argument:**

*bad***if(**

*Sales are greater than $5,000*

**,**

*2% Commission*

**,**

**if(**

*Sales are greater than $10,000*

**,**

*5% Commission*

*, No Commission*

**))**

Can you guess why?

If an employee has sold $12,000 worth of Sales, then the first set of criteria is met, and the result will be 2% commission. However, this does

*not*meet the rules that we want for employees over $10,000.

This is also a

*way of preparing the argument:*

**bad****if(**

*Sales are greater than $10,000*

**,**

*5% Commission*

**,**

**if(**

*Sales are greater than $5,000*

**,**

*2% Commission*

*, No Commission*

**))**

It gets closer to the order of arguments. Our employee who hit $12,000 will get the correct commission, our employee who hit $8,000 will also get the correct commission, but what about the employee who just hit $5,000?

Our second criteria is, If greater than $5,000... But $5,000 is

*not*greater than $5,000. They will not make the commission they expected

So, this is a

*good**simple*way of thinking about this argument

**if(**

*Sales are less than $5,000*

**,**

*no Commission*

**,**

**if(**

*Sales are less than $10,000*

**,**

*2% Commission*

*, 5% Commission*

**))**

This set the parameters to have everyone not hitting a certain tier kept at the tier below.

We could also use

**if(**

*Sales are greater than $9,999.99*

**,**

*5% Commission*

**,**

**if(**

*Sales are greater than $4,999.99*

**,**

*2% Commission*

*, no Commission*

**))**

So let's open up our Team Performance report...

and let's remove the measures we don't need:

*"Total Hours", "Sales per Hour", "Profit per Hour",*and

*"Basket Size"*

Let's click on the grey "Calculations" button to open up the table calculations...

...and start our first set of criteria: if sales is less than $5,000...

then we'll enter a comma, and start our second criteria: if sales is less than $10,000

finally, the last rule where if criteria 1 and 2 are both not met: (e.g. if sales are $10,000 or greater)

To complete the calculation, we need to add

*two*closing brackets. One for each if argument that we started with...

Let's make this a dollar format, and give this Calculation a meaningful title...

So now, if I have prepared the calculation properly, when I save it...

It will show up with meaningful numbers.

*Continue to Part 2*

Joseph McKeown

Lightspeed Analytics and reporting consultant

Lightspeed HQ

Lightspeed Analytics and reporting consultant

Lightspeed HQ

#### Recent Discussions

#### Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

## Quick Links

#### Popular Tags

- api 145
- eCom 78
- retail 77
- eCommerce 44
- integrations 33
- Lightspeed Analytics 31
- Lightspeed 31
- sales 30
- support 21
- apps 21
- ecom api 20
- shipping 20
- Product Development 19
- Lightspeed Payments 16
- theme 16
- Custom Field 14
- workarounds 14
- producten 14
- product 14
- Online Store 13
- receipts 13
- Restaurant 13
- tips 13
- themes 13
- omnichannel 12