Offsettng Rows

[Balllu]

Hi All,

i am stuck with one problem and i am here to get some help. i am trying to offset the line which have amount with opposite signage.

to offset the line criteria is first it offset the line with current month with previous than current month to current month(raw data have some old months data also).

in some cases where the current month's entry offsets with the sum of two or more rows from the previous month. can you help me with the workflow. Earlier i am using prescriptive optimization tool but it getting stuck on 36% and not move forward. can't summarize as we need line to line records.

input data:- 

Record	Status	Year. Month	HT Amount
1	400136150	2024.09	-5699200
2	400635850	2024.10	15000
3	400136150	2024.11	-5699200
4	400136150	2024.11	5699200
5	400635850	2024.11	-30000
6	400635850	2024.10	15000

output:-

Record	Status	Year. Month	HT Amount
3	400136150	2024.11	-5699200

[PeterHolmes01]

You could use the ABS() function to get an absolute amount on all values and then do a sort by this field. You could then use the multi field formula to net off row that are equal to each other and then use a filter to get the row you needed.