[tonystremain]

I would like to create a saved search to help us calculate commissions on sales.

We have ranges with a different percentages, but the difficulty I'm having is that it's sliding scale – not straight.

So if the bands are say 0-1000 = 1%, and then 1001 and above is 2%, and the sales volume is 1,500, the total would be 20 (1000*1%) + (500 *2%). Not 1500 * 2%.

We have an Excel spreadsheet using SUMPRODUCT formula but I'm not sure if this can be re-created in NetSuite.

I would like one version of the data instead of having to populate this spreadsheet.

Thanks for any help or guidance.
This is a cached copy. Click here to see the original post.

[pcutler]

If you have the advanced reporting module, you might want to consider using ODBC/JDBC instead. That said, depending on what records you are reporting on you may be able to implement a saved-search-only solution by adding formulacurrency columns such as the follows:

CASE

WHEN {salesrep.custentity_sales_volume} <= 1000 THEN 0.01*{total}

WHEN {salesrep.custentity_sales_volume} > 1000 AND {salesrep.custentity_sales_volume} <= 1500 THEN 0.02*{total}

….

ELSE 0

END

Note that this formula relies on a custom field custentity_sales_volume on the sales rep that specifies their sales volume.

[girieshg]

Why not use a Commission Schedule with a Calculation Scale set to "Marginal"?

[tonystremain]

I'm not sure if we have the advanced reporting module. I've checked our enabled features and couldn't see any reference to that. We also don't have the commission module.

For now, I've created multiple columns with a formula currency, so with the example above:

1. case when sum({netamountnotax}-{shippingamount}) < 1000 then sum({netamountnotax}-{shippingamount})*0.1 when sum({netamountnotax}-{shippingamount}) > 1000 then (1000*0.1) else 0 end

2. case when sum({netamountnotax}-{shippingamount}) > 2000 then 0.2*(sum({netamountnotax}-{shippingamount})-1000) else 0 end

I would like to just have one column with just the total commission but I can't seem to get it to work, so this will have to do for now!

My criteria is a saved transaction search, with main line set to true, transaction type set to invoice & credit memo.

[khultquist]

The key to simplifying this is to rewrite your commissions with cumulative percentages, as follows:

(1% of the total) + (1% of the total over $1,000)

This will give the same results, but it's much easier to calculate.

In SQL, this would look like

Code:
0.01 * ({netamountnotax}-{shippingamount})
+
0.01 * (case when {netamountnotax}-{shippingamount} > 1000 then {netamountnotax}-{shippingamount} – 1000 else 0 end)
Also you should consider making a calculated Transaction Body Field called {custbody_netamount}, set it to not store value. Then you can shorten the SQL considerably:

Code:
0.01 * {custbody_netamount}
+
0.01 * (case when {custbody_netamount} > 1000 then {custbody_netamount} – 1000 else 0 end)

Note that this is easily expanded too, so let's say you have another commission threshold at $3,000 for an extra percent; then you just add another value of + (1% of the total over $3,000)

Code:
0.01 * {custbody_netamount}
+
0.01 * (case when {custbody_netamount} > 1000 then {custbody_netamount} – 1000 else 0 end)
+
0.01 * (case when {custbody_netamount} > 3000 then {custbody_netamount} – 3000 else 0 end)

[tonystremain]

Thank you for your suggestion. I like the idea of using a calculated transaction body field to help shorten the SQL. So I guess the calculation would simply be {netamountnotax}-{shippingamount} and I would replace that custom field with those references in the SQL?

I also tried your suggestion for cumulative percentages and I can't get it to work! Here is the complete SQL as I have it now which is working accurately:

Code:
case when sum({netamountnotax}-{shippingamount}) < 6000 then sum({netamountnotax}-{shippingamount})*0.05
when sum({netamountnotax}-{shippingamount}) > 6000 and sum({netamountnotax}-{shippingamount}) < 12000 then 0.05*(sum({netamountnotax}-{shippingamount})-6000)+(6000*0.05)
when sum({netamountnotax}-{shippingamount}) > 12000 and sum({netamountnotax}-{shippingamount}) < 18000 then 0.06*(sum({netamountnotax}-{shippingamount})-12000) +(12000*0.05)
when sum({netamountnotax}-{shippingamount}) > 18000 and sum({netamountnotax}-{shippingamount}) < 24000 then 0.07*(sum({netamountnotax}-{shippingamount})-18000)+(12000*0.05)+(6000*0.06)
when sum({netamountnotax}-{shippingamount}) > 24000 and sum({netamountnotax}-{shippingamount}) < 30000 then 0.08*(sum({netamountnotax}-{shippingamount})-24000)+(12000*0.05)+(6000*0.06)+(6000*0.07)
when sum({netamountnotax}-{shippingamount}) > 30000 and sum({netamountnotax}-{shippingamount}) < 36000 then 0.09*(sum({netamountnotax}-{shippingamount})-30000)+(12000*0.05)+(6000*0.06)+(6000*0.07)+(6000*0.08)
when sum({netamountnotax}-{shippingamount}) > 36000 then 0.1*(sum({netamountnotax}-{shippingamount})-36000)+(12000*0.05)+(6000*0.06)+(6000*0.07)+(6000*0.08)+(6000*0.09) else 0 end

[khultquist]

try this (looks like your first two lines are both at 5%)

Code:
0.05 * ({netamountnotax}-{shippingamount}) +
0.00 * (case when {netamountnotax}-{shippingamount} > 6000 then {netamountnotax}-{shippingamount} – 6000 else 0 end) +
0.01 * (case when {netamountnotax}-{shippingamount} > 12000 then {netamountnotax}-{shippingamount} – 12000 else 0 end) +
0.01 * (case when {netamountnotax}-{shippingamount} > 18000 then {netamountnotax}-{shippingamount} – 18000 else 0 end) +
0.01 * (case when {netamountnotax}-{shippingamount} > 24000 then {netamountnotax}-{shippingamount} – 24000 else 0 end) +
0.01 * (case when {netamountnotax}-{shippingamount} > 30000 then {netamountnotax}-{shippingamount} – 30000 else 0 end) +
0.01 * (case when {netamountnotax}-{shippingamount} > 36000 then {netamountnotax}-{shippingamount} – 36000 else 0 end)

[tonystremain]

As soon as the total goes over 12000 then the calculation doesn't work. It's only calculating 5% on the total and nothing else.

I.e. Sales total = 22804.89. Expect commission is 1293.34. Calculated is 1140.24.

[khultquist]

I suspect that the data types are different or there are nulls, and this is throwing off the logic. I see that you are using sum() in your statements, that might be converting currency type to float.

If you do end up using a custom field, you can add that that to the calculation. And add some handling for null. Something like this: sum(NVL({netamountnotax},0)-NVL({shippingamount},0)

[tonystremain]

Adding SUM to the calculations has done the trick. It loads much faster than the one I had originally created – up to 50% quicker! Thanks.

I'm also trying to implement a custom transaction body field with the calculation as you suggested and I get ERROR: Invalid Expression.

I think the change in SQL is enough for now, since I'm not sure how much quicker it will make the search to load.

[Author]

Posts