Solved

Posted on 2012-08-30

Ok experts this one has me stumped.

As I mentioned in a prior question I have a commission report that runs total commissions paid - actually this one is overrides for managers of salespeople but basically the same thing. It is already built and runs for a 30 day time frame, the 16th of "Last Month" to the 15th of "current month" Each sales manager receives a commission that is a factor generated by different criteria per manager, for example one might get a factor of 1 for some sales people under them and a factor of 2 for others, this factor is then multiplied by the total of all closed loans in that commission period, this is all fine. I handle this with a separate formula per manager in the group 1 header that sets the factor, then in the details section I have a formula per manager that multiplies the total sales amount by the factor, giving me a breakdown of commission paid per sale and a total in the footer that adds them all up.

But now I have a person who's factor is calculated based on the total sales Year to Date. I inserted a sub report to get the total sold to date but now I have a problem, the factor for this manager will adjust downward once 1 billion in sales is hit, and I need to apply that factor from that dollar amount onward, not for the whole commission period. So in other words if 1000 sales were closed during the commission period but the 5th sale is the one that put the YTD total over 1 billion then the first 4 sales would be calculated at a factor of 3, and the rest at a factor of 1. So I need to know the running total of the YTD sales not just the final total right? How do I get that?

As I mentioned in a prior question I have a commission report that runs total commissions paid - actually this one is overrides for managers of salespeople but basically the same thing. It is already built and runs for a 30 day time frame, the 16th of "Last Month" to the 15th of "current month" Each sales manager receives a commission that is a factor generated by different criteria per manager, for example one might get a factor of 1 for some sales people under them and a factor of 2 for others, this factor is then multiplied by the total of all closed loans in that commission period, this is all fine. I handle this with a separate formula per manager in the group 1 header that sets the factor, then in the details section I have a formula per manager that multiplies the total sales amount by the factor, giving me a breakdown of commission paid per sale and a total in the footer that adds them all up.

But now I have a person who's factor is calculated based on the total sales Year to Date. I inserted a sub report to get the total sold to date but now I have a problem, the factor for this manager will adjust downward once 1 billion in sales is hit, and I need to apply that factor from that dollar amount onward, not for the whole commission period. So in other words if 1000 sales were closed during the commission period but the 5th sale is the one that put the YTD total over 1 billion then the first 4 sales would be calculated at a factor of 3, and the rest at a factor of 1. So I need to know the running total of the YTD sales not just the final total right? How do I get that?

6 Comments

The commission is paid individually per sale as the sale amount times a factor, in this manager’s case each sale is paid at 3 basis points, or {salesamount} * .0003, however once the total sales for the year, not the commission period passes 1 billion dollars the factor for all additional sales changes to 1 basis point. So if 1,000,000.00 was sold in this commission period and the year to date sales volume surpassed 1 billion during that period as well, the basis point factor would need to change from 3 to 1 as of the sale that caused the total sold Year to Date to surpass 1 billion. For example if there were 10, 100,000.00 sales for a total of $1million and the fifth sale put the YTD total over 1 billion then the first 5 sales of this period would be paid at 3 basis points ( 100,000.00 * .0003 = $30 each) but the remaining 5 would be paid at 1 basis point ( 100,000.00 * .0001 = $10 each) as would all future overrides on all future commission periods until Year end when the YTD total would refresh.

> For example if there were 10, 100,000.00 sales for a total of $1million and the fifth sale ...

I was reading that as "10.1 million sales for a total of $1million", which didn't make much sense. Then I figured out that it was "ten 100,000.00 sales for a total of $1million". Just thought I'd mention that, in case anyone else was confused.

You seem to be contradicting yourself. At the end of your first post, you said that the sale that puts them over the limit (the 5th sale) should get the lower factor. But in your last post, you say that the sale that puts them over the limit (also the 5th sale) should get the original (higher) factor. Which is it?

Putting that question aside for the moment, it seems like you could do this using a formula. That formula would check a running total, or a variable with the total in it, and choose a factor based on that. Exactly what total you check would depend on which factor should be used on the sale that puts them over the limit.

James

If the company you could have a subreport in the report header that calculates sales for YTD where the date is 15th of last month.

If it is the manager then the subreport would be in the manager group header.

You can also include the data for the year and suppress records that aren't inthe selection range.

Basically you need a formula like

WhilePrintingRecords;

Global NumberVar TotalSales;

Global NumberVar CommisionRate;

Global NumberVar TotalCommision;

If TotalSales > 1000000000 then

CommisionRate := 0.01

Else

CommisionRate := 0.03;

TotalSales := TotalSales + {Salesfield};

TotalCommision := TotalCommision + {Salesfield} * CommisionRate;

""

mlmcc

By clicking you are agreeing to Experts Exchange's Terms of Use.

Join the community of 500,000 technology professionals and ask your questions.

Connect with top rated Experts

**10** Experts available now in Live!