📉 Create a table as a Variable for Tier or Linear functions
Updated
Below are the steps to follow if you want to create a table as a variable, and how to use it with the function TIER.Â
1. Click on "Designer" (1), "Expand a rule" (2) and then click on “Create a Variable” (3).
2. Fill the appropriate name, context, and select the format “Table”. Make sure the option “BUILDER” is ticked (1 on the screenshot). You can add and delete rows and columns easily with (2) and (3). You can also select the data type with (4). You should have the following image.
3. Now it is time to explain how to fill this table. We will always put on the first column (to the left)Â the minimum values, then the second column the maximum value, and third the corresponding value.Â
It can be confusing but this example will help you. In green the minimum value, blue the maximum value, and red the corresponding value.
The following table is a compensation decelerator/accelerator in function of performance. 100% in “Achievement” line is the target, “Monthly” line is the decelerator/accelerator on bonus compensation.Â
Achievement | 0<X< 50% | 50% < X < 90% | 90% < X < 110% | 110% < X< Infinity |
Monthly | 0 | x0.9 | x1 | x1.2 |
This table would then look like that in the Designer:Â
N.B. we can use “Infinity” for infinite values. Don’t forget the capital “I” at the beginning or it will not work.
4. Using the TIER function with the table
As shown in the following screenshot, TIER is used in the following way:
We are now going to break down the formula:
TIER (tierToCompare, table)
In this specific case, tierToCompare is a variable, which is the achievement of the salesperson. Let’s say is it called user.achievement, and that it is equal to 60%. table is the name of the table you just created. In our example, it is called myFirstTable.
We would therefore write :
TIER(user.achievement, myFirstTable)
As user.achievement = 60%, we have 50% < user.achievement < 80%
We have a decelerator of 70%.
