I'm trying to compute sales commissions in R for a couple of sales reps. The data is structured as follows:
Table 1: Sales figures for each rep. Each sale has a discount associated with it that the rep negotiates with the customer.
Table 2: Commissions table. Specifies the commission earned depending on 1) the discount and 2) product range (e.g. soft drinks, booze, etc.).
EDIT with actual data:
Table 1:
| sales rep name | product range code | product range description | order number | discount granted to customer in the order (%) | sales amount |
|---|---|---|---|---|---|
| John Doe | A01 | soft drinks | XS48513 | 5 | 1000 |
| John Doe | A01 | soft drinks | XS48514 | 20 | 5000 |
| John Doe | A01 | soft drinks | XS48515 | 1 | 2000 |
| John Doe | A01 | soft drinks | XS48516 | 0 | 1000 |
| John Doe | B05 | booze | XS48517 | 4 | 2000 |
| John Doe | B08 | water | XS48518 | 0 | 1500 |
Table 2:
| product range code | product range description | discount from (%) | discount to (%) | commission | sales rep name |
|---|---|---|---|---|---|
| A01 | soft drinks | 0 | 5 | 0,5% | John Doe |
| A01 | soft drinks | 5 | 10 | 0,7% | John Doe |
| A01 | soft drinks | 10 | 100 | 0,0% | John Doe |
| B05 | booze | 0 | 5 | 0,1% | John Doe |
| B08 | water | 0 | 100 | 0,0% | John Doe |
To compute the earned commission by each rep I need to multiply the sales_amount by the appropriate commission. This commission is different for each product range and for each discount tranche.
So in this case, the earned commission for the first order would be 0,5% of 1000 usd. The second commission would not generate commissions as 0% of 5000 usd equals 0.
I am trying to avoid writing all the combinations using if structures (which would take a while...)
Thanks in advance
