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

josephmckeownjosephmckeown Posts: 52Moderator, 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 of using multiple IF functions


Part 2



We have our calculated commission numbers, but if we're reading this report, it may not be clear that each employee has the right commission. To help make this report more meaningful, let's add some text labels...

So let's go back to the grey "Calculations" button, and click on "Add Table Calculation"



And because the text labels should follow the same rules as the numeric calculation, let's start by copy+pasting the formula from the first field...


...into the second field...


..,and now changing the arguments.

So, if an employee's sales are less than $5,000, the label should read: "No Commission". So between quotation marks, let's add that label. (We could include more detail in the label, i.e. "less than $5,000, no commission", but you get the idea)



As you change the results from numbers to labels, you will see this error message from Analytics. Its own criteria needs the value types to match. But as we continue our argument, it will be resolved.

So, our second label is for sales below $10,000 (but not below $5,000)...


And finally...


So now, if I have prepared this calculation properly, when I saved my calculated fields...


As an added bonus, you can also drag calculated fields around, in case they are easier to read in a different order

Joseph McKeown
Lightspeed Analytics and reporting consultant
Lightspeed HQ
Sign In or Register to comment.