Offsettng Rows

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

Input

Output

Accepted answers

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.

All comments

PeterHolmes01

OTrieger

@Balllu
Have you tried using Summarized tool, Group data on Status and Sum HT Amount, add a filter tool to remove all the lines that contains 0

Balllu

summarized tool make the current month HT Amount 0 that i don't want , i need first offset the current month HT Amount to previous month HT Amount.

Balllu

if possible can you pls add workflow for this. as i have three row for same amount and i need to eliminate first November amount with previous month and left with only one row which is for november.

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703