Amount Distribution

[John Webster]

I've done something similar a couple of times, but it was a wee while ago.
Here's the outline of the concept - the detail I'll leave to someone else...

In your example:
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

or PO-DIST / PO-MST * PAYMENT = AMOUNT

Change this to be
PO-DIST / (PO-MST - PO-DIST so far) * (PAYMENT - AMOUNT so far)

This can be done using temps for PO-DIST so far and AMOUNT so far. Do the
calculation, then use item statements to update the temps. The first time
round the temps are zero, so Record 1 is the same as you have.
For record 2 the calculation becomes
(1,204.34 / (3,900.00 - 1,000.00)) * (13.19 - 3.38)
and for record 3
(1,695.66 / (3,900.00 - 2,204.34)) * (13.19 - 7.45)

which of course comes to 1,695.66 / 1,695.66 = 1, so that the final
calculation always gives what's left to be distributed.

something like...
temp t-po-dist-so-far num*10
temp t-amount-so-far num*10
...
item AMOUNT = PO-DIST / (PO-MST - t-po-dist-so-far) * (PAYMENT -
t-amount-so-far)
item t-po-dist-so-far subtotal PO-DIST
item t-amount-so-far subtotal AMOUNT

You'll maybe need to sort out the resetting, but the technique does work
wonderfully well.

John W
--------
From:	Terry Pickering
Sent:	July  4, 2000 9:19 AM
To:	powerh-l@sphere.swau.edu
Cc:
Subject:	Amount Distribution

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.

= = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = =
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.