Multiple Field Formula

[ebledsoe22]

Hello,

I am trying to use the Multiple Field Formula tool in order to add/subtract values in 3 rows together. I am working on an apportionment workflow where my first row is my gross unadjusted data and the two subsequent rows are adjustments that need to be added to the first row. How do I create my formula in the expression tool to get my desired result?

[BS_THE_ANALYST]

Since you are effectively adding all the rows together, wouldn't the Summarize tool suffice here?

[ShankerV]

Hi @ebledsoe22 

You ask is possible by Multi row formula tool.

However can you post sample input and output expected so relevant formula can be suggested to get the desired result.

[FinnCharlton]

Hi @ebledsoe22,

It sounds to me like you need to use the multi-ROW formula tool instead of the multi-FIELD. You can reference rows above and below using this tool. If you need some further help please attach some sample data and I'll help you write the formula.

[ebledsoe22]

Hi @BS_THE_ANALYST I only need to apply the formula to 3 out 14 rows, not the entire data set.

[FinnCharlton]

You could think about using the sample tool to take only the first 3 rows, then either use a multi-row formula or a summarize tool like @BS_THE_ANALYST has suggested. You might also be able to CrossTab your data to turn the rows to columns, and then use a normal formula tool. Again some sample data would help.

[ebledsoe22]

Hi @ShankerV ad @FinnCharlton, I have 14 rows, but almost 60 columns due to this being a state apportionment project. Across the top are my states, and then my rows are labeled according to the different factors. I need this formula applied to all of the state columns, but only on 3 rows. Row 1 is the base inventory number, wile rows 2 and 3 are adjustments. I want to add the adjustments to the gross value for each state.

[BS_THE_ANALYST]

@ebledsoe22 okay, we can add a record ID to the data. We can then filter out the records you want to select using the Filter tool. We can then apply the Summarize tool. See attached mock data ..

[BS_THE_ANALYST]

Alternatively, you can use the Select Records tool which gives more intuitive flexibility to select the records you want to sum together. See attached

[ShankerV]

Hi @ebledsoe22 

As you want to apply the sum for all 60 columns, multi field formula can't help.

You need to filter all the first 3 rows.

Do the Cross tab tool to do in all on row.

Then apply the multi row formula and then Transpose back.

Join to the original dataset.

Many thanks

Shanker V