Balancing Calculation Process and Matching Pairs Output

[k3pineapple]

Hi Experts,

Need your assistance to build a little complex calculation model, there are two steps for it

List value (Amount Column) split into two side: Positive and Negative, comparing the absolute value between the sum of positive and sum of negative value first to decide which is the base value to start the calculation. (higher minus lower,)

2nd step:

Take the 1st value(Earliest date value) from either positive list or negative list (Decided by which side has higher absolute value), saying side A

a) minus the other side (side B) first value

b) the reminder minus side B 2nd value and keep going until the first value become 0

c) take the 2nd value in side A, minus the reminder from side B value.

d) repeat step a-c

Additional condition:

1. The calculation direction (A ->B or B->A) decided by date. i.e, if A side minus B side, the value date A >= B, otherwise stop)

2. continue with condition 1, if the calculation stop, calculation direction switch.

Example:

Raw Data

Cdate	PastDate	Value	User	Product
20221101	20220910	50	A	Apple
20221101	20220913	20	B	Apple
20221101	20220915	30	C	Apple
20221101	20220915	40	D	Apple
20221101	20220915	50	E	Apple
20221101	20220915	-10	F	Apple
20221101	20220918	-20	G	Apple
20221101	20221001	-60	S	Apple
20221101	20221002	-300	G	Apple
20221101	20221003	-300	G	Apple
20221101	20221003	-30	S	Apple
20221101	20221003	20	F	Apple
20221101	20221005	-20	D	Apple
20221101	20221006	300	A	Apple
20221101	20221007	300	S	Apple

In Manual Process: Value will be splited, 

Side A	Ref	Cdate	PastDate	Value	User	Product
	100005	20221101	20220915	-10	F	Apple
	222003	20221101	20220918	-20	G	Apple
	22233	20221101	20221001	-60	S	Apple
	330000	20221101	20221002	-300	G	Apple
	322201	20221101	20221003	-300	G	Apple
	353330	20221101	20221003	-30	S	Apple
	322222	20221101	20221005	-20	D	Apple
			Sum	-740

Side B	Ref	Cdate	PastDate	Value	User	Product
	100001	20221101	20220910	50	A	Apple
	10002	20221101	20220913	20	B	Apple
	100002	20221101	20220915	30	C	Apple
	200010	20221101	20220915	40	D	Apple
	300005	20221101	20220915	50	E	Apple
	130001	20221101	20221003	20	F	Apple
	32222	20221101	20221006	300	A	Apple
	315532	20221101	20221007	300	S	Apple
			Sum	810

With calculation absolute value of the sum of both side,  Side B > A, Start with B

Calculation Details: Image Attached

Sample out: Showing Calculation Pairs

Pair	Ref	Cdate	PastDate	Value	User	Product
1	100001	20221101	20220910	50	A	Apple
1	100005	20221101	20220915	-10	F	Apple
1	222003	20221101	20220918	-20	G	Apple
1	22233	20221101	20221001	-20	S	Apple
2	10002	20221101	20220913	20	B	Apple
2	22233	20221101	20221001	-20	S	Apple
3	100002	20221101	20220915	30	C	Apple
3	22233	20221101	20221001	-20	S	Apple
3	330000	20221101	20221002	-10	G	Apple
4	200010	20221101	20220915	40	D	Apple
4	330000	20221101	20221002	-40	G	Apple
5	300005	20221101	20220915	50	E	Apple
5	330000	20221101	20221002	-50	G	Apple
6	330000	20221101	20221002	-200	G	Apple
6	130001	20221101	20221003	20	F	Apple
6	32222	20221101	20221006	180	A	Apple
7	322201	20221101	20221003	-300	G	Apple
7	32222	20221101	20221006	120	A	Apple
7	315532	20221101	20221007	180	S	Apple
8	353330	20221101	20221003	-30	S	Apple
8	315532	20221101	20221007	30	S	Apple
9	322222	20221101	20221005	-20	D	Apple
9	315532	20221101	20221007	20	S	Apple
10	315532	20221101	20221007	70	S	Apple

Much Appreciated that you may help me with it. :)

[DawnDuong]

hi @k3pineapple 

While I understand what you are trying to do, I think the logic will not work all the times (i.e. it cannot be generalised)

The current logic offsets the next number as long as it is smaller than the residual value after subtracting previous values. This logic is flawed because the next "start" value can occur before the previous "start" has been fully amortised and there is no guarantee that the "start" is of the same +/- sign than the previous start.

What you can get out of this logic is at best a guess (which will likely fail when the data gets larger).

I would suggest that you check with the IT/Data team to see whether there are additional fields that can be used to do a more reliable mapping.

Regards,
Dawn.

[k3pineapple]

@DawnDuong Thank you very much for the advise here.

Unfortunately, there is no such relative data/columns that we have in order to use here. that's the reason why we are still do the calculation manually. 

Wondering whether it may work if we build the workflow into several sections. 

Workflow I, comparing two sides, sorting the data

Workflow II, generate the pair one by one, by using the sorted data

Workflow III, unite the result

Thank you