Create a new row by adding rows

Question

I am trying to create a new row by adding/subtracting two rows: I am subtracting row 1520 from row B00030 to get "new row"

1000          75,068,849.29                 20.211200                   17,830.64 1500                              9.08     123,139,844.48      647,535,943.85       936,622.961520     5020          16,596,474.10 4000     4200     B00030      (534,649,674.31)     121,790,923.38    (270.09)       70,623,266.96   1,817,934.676600000         417,366,011.00          495,259.006600600           17,872,759.00    6601000           92,483,610.22               12,401.38   6601110                 301,225.03               12,886.01   6601120                         502.52             145,773.23   B00005   (1,064,689,379.58)     121,631,834.76    (270.09)       70,623,266.96   1,322,675.67      New Row      (534,649,683.39)       (1,348,921.10)    (270.09)   (576,912,676.89)       881,311.71

any help is appreciated!

DavidSta · Answer

Hi @NicoleHou,

one option would be to calculate across different rows with the Multi-Row Formula tool. But with this you always have to make sure the entries have the same sorting and no new entries are added later.

Attached you can find a sample workflow which can work dynamically by pivoting the table multiple times.

1) As you want to calculate across different rows and you have multiple columns where you want to apply it I recommend to pivot the table. With this you get the items you want to calculate with (plus/minus ...) in columns instead of rows. This happens with the first Cross-Tab and Transpose Tools.

2) Then you can do the calculation very easily with a formula tool.

3) When this is done you pivot this table back to the original format with a Transpose and Cross-Tab.

4) To get the original sorting order you add the RecordID which was added directly at the beginning to the end and use it for sorting the items.

The general recommendation to create a dynamic workflow for such challenges is:

* when you want to apply formulas: bring the items you want to use for the calculation to columns
* when you want to aggregate something: bring the items you want to aggregate in a single column but multiple rows

Here the Cross Tab and Transpose Tools are your best friend.

Best regards,

David

1120875.yxmd