Using Running total to calculate cut offs

[VBAnewguy]

I am writing a report that lists records like this:

Date Company Reveue Commish(5%) Run Tot
12/1/04 A 10,000 500 500
12/2/04 A 20,000 1,000 1,500
12/3/04 A 16,000 800 2,300
12/4/04 A 12,000 600 2,900

I want to cap the amount of commishion paid at 2,000. I would like for report to look like this:

Date Company Reveue Commish(5%) Run Tot
12/1/04 A 10,000 500 500
12/2/04 A 20,000 1,000 1,500
12/3/04 A 16,000 300 2,000
12/4/04 A 12,000 0 0

Not sure if this is possible, I have been attempting it all morning.... any advice would be great.

 

[VBAnewguy]

oops, supposed to say "Revenue

 

[lbass]

I would think that the 12/4 commission total should show 2000 as well, since it is a running total. If that is okay, then the following works. Create two formulas:

//{@reset} to be placed in the company group header (or the month
//group header, if the limit is for the month):
whileprintingrecords;
numbervar commiss := 0;

//{@accum} to be placed in the detail section:
whileprintingrecords;
numbervar commiss;

if commiss + {@commission} <= 2000 then
commiss := commiss + {@commission} else
commiss := 2000;

If you want to display the commission at the group level, create a third formula:
//{@display}:
whileprintingrecords;
numbervar commiss;

-LB

 

[VBAnewguy]

Thanks LB,

That would work well to cap the commission at 2000. Now, what would really help would be able to calculate the commission on the 12/3 and 12/4 entries. Currently, I use a standard formula to calculate that amount (Revenue * .05).

BUT, as you can see, the commission amount changes for 12/3 entry. So, basiacally, it is 5% of the revenue UNLESS the commision amount hits 2000, so, calculating the partial commission amount for the 12/3 entry is the next challenge. I would think I could use something like:

If ((REVENUE * .05) + RunningTotal) > 2000 then
RunningTotal - (REVENUE * .05)

But, I just can't seem to get the syntax down to get this to work.....

Once again, thanks for the help with this guys!

 

[lbass]

Try:

If ((REVENUE * .05) + RunningTotal) > 2000 then
2000 - RunningTotal

-LB