Amount Distribution

[Terry Pickering]

I have a situation where I need to distribute amounts based upon
percentages of the total. Rounding works for "most of the time". But there
are cases where I need to be guaranteed the "sum of the parts" equals the
total.

Here is the situation:

I have a purchase order record for $3,900.00 (stored internally 390000 in 
an integer*10 field) in the PO-MST file. It was distributed across 3 
accounts in the PO-DIST file as follows:

Record 1:	$1,000.00
Record 2:	$1,204.34
Record 3:	$1,695.66

Total:		$3,900.00

The distributions were originally entered using a QUICK screen and it was 
fairly easy to ensure the distributions added up to the total.
Now comes the fun part - time to pay on this PO. They can make multiple 
partial payments and the payments need to be distributed in the same 
percentage ratio as the original distribution. In this case, a payment of 
$13.19 is made. The amount to be distributed is
calculated by finding the percentage of the total for each record, then 
multiplying that times the invoice amount.

Record 1: (1,000.00 / 3,900.00) * 13.19 = 3.38
Record 2: (1,204.34 / 3,900.00) * 13.19 = 4.07
Record 3: (1,695.66 / 3,900.00) * 13.19 = 5.73

Total: 13.18 or .01 short of the total.

I have tried using rounding on the "percentage" calculation and sometimes 
that helps, but not always. I want to insure that all of the individual 
records add up to the original amount. The process that does these 
calculations is in QTP. What I have attempted to do using temporary fields 
is to keep a running total of the amounts, then at the sort break to "add 
in the difference". This can of course be a negative amount if the total 
was higher than the total amount. I've been successful except I can't seem 
to figure out when to know what the "last record" is to add in my fudge 
factor. I've tried at sort breaks  but at that point either the record is 
not available or the counters have been reset.

Any suggestions would be welcome - except I don't relish the thought of 
doing another pass on the data......



~~~~~~~~~~~~~~~~
Terry A. Pickering
Portland, Oregon
pickering@myself.com
~~~~~~~~~~~~~~~~
= = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = =
Subscribe: "subscribe powerh-l" in message body to majordomo@lists.swau.edu
Unsubscribe: "unsubscribe powerh-l" in message to majordomo@lists.swau.edu
This list is closed, thus to post to the list, you must be a subscriber.