Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have a chart in where I calculate the SalesAmount per Salesperson.
This is a calculation based on a lot of different sales figures, filtered with set analysis to get the correct result.
It is not doable via scripting, so in my script, I do not have the correct SalesAmount per Salesperson
this is an example of the result of the calculation in the chart, but be aware, this is a calculation of a lot of different sales amounts:
Salesperson | SalesAmount |
---|---|
Dave | 5000 |
Jef | 10000 |
Michael | 15000 |
Now I have a table (Excel spreadsheet) where I upload a "SalesAmount" and "Bonuspercentage".
(so salesamount between 0-2500 = 5%, between 2501-12500 = 10%, between 12501-20000 =15%)
SalesAmount | BonusPercentage |
---|---|
2500 | 5 |
12500 | 10 |
20000 | 15 |
I would like to link them together, so as a final result I would like to see:
Salesperson | SalesAmount | BonusPercentage |
---|---|---|
Dave | 5000 | 10 |
Jef | 10000 | 10 |
Michael | 15000 | 15 |
Is there a way to get the correct "BonusPercentage" based on the calculated field "SalesAmount" in the chart?
I got a little lost trying to do exactly what you said, so I tried a slight workaround. Instead of using a table with SalesAmount and BonusPercentage, I used a table with BonusStart, BonusEnd, and Bonus Percentage. Hopefully you can make that adjustment in your Excel, but if not, you can use Peek() to do it in your script. There may be a way to get this done with your original data structure but I'm not sure how.
Load * Inline [
Salesperson, SalesAmount
Dave, 5000
Jef, 10000
Michael, 15000
Wile E. Coyote, 1000];
Load * Inline [
BonusStart, BonusEnd, BonusPercentage
0, 2500, 5
2501, 12500, 10
12501, 20000, 15
];
Note that this is a data island - it does not connect to SalesAmount (which you don't want it to). I then used the following expression:
Sum(aggr(if(Sum(SalesAmount)>=BonusStart AND Sum(SalesAmount) <= BonusEnd,BonusPercentage),BonusStart,Salesperson))
Note that this will also work if the bonus table is connected to the sales table via dimensions such as Salesperson:
Load * Inline [
Salesperson, BonusStart, BonusEnd, BonusPercentage
Dave, 0, 2500, 5
Dave, 2501, 12500, 10
Dave, 12501, 20000, 15
Jef, 0, 2500, 11
Jef, 2501, 12500, 22
Jef, 12501, 20000, 33
Michael, 0, 6666, 6
Michael, 6667, 66666, 66
Michael, 66667, 666666, 666
];
May be like this:
If(Column(1) >= 0 and Column(1) <= 2500, "5",
if(Column(1) > 2500 and Column(1) <= 12500, "10",
If(Column(1) > 12500 and Column(1) <= 20000, "15")))
Column(1) refers SalesAmount.