Analytics Calculations: Simple Commission II (IF Statements 1.1)
josephmckeown
Posts: 39Moderator, Lightspeed Staff 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: (including the punctuation)
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...
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 "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
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...
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
Simple IF functions are always constructed the same way in Analytics: (including the punctuation)
if( criteria , result if true , result if false )
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 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)
A note on "greater than/ less than" logic
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
Joseph McKeown
Lightspeed Analytics and reporting consultant
Lightspeed HQ
Lightspeed Analytics and reporting consultant
Lightspeed HQ
Tags: