running total for a date range outside the range of a crystal report
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?