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

josephmckeownjosephmckeown Posts: 44Moderator, Lightspeed Staff moderator
edited May 2018 in Reporting & Analytics
*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 bad way of preparing this argument:
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 bad way of preparing the argument:
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
Sign In or Register to comment.