Multiple Field Formula

One way of doing this,

Input was:

Output is:

Workflow:

Hope this helps!!!

If you need the workflow let me know, I will explain the steps in detail as I am unable to save the workflow to attach here.

Many thanks

Shanker V

All comments

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

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.

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.

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.

@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 ..

multifieldansweryxmd.yxmd

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

multifieldansweryxmd.yxmd

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

FinnCharlton

I think @BS_THE_ANALYST has suggested a good method, though I'd add that it might be worth thinking about transposing your data. In the long run it would be more useful to have your states as rows and your metrics as columns, as problems like this could then be dealt with in a single formula tool. It would probably also help with any further analysis.

@BS_THE_ANALYST Thank you for your response. I do have a record number column. The Gross inventory number is record number 1. The first adjustment is record number 1.1 and the second adjustment is record number 1.2. Is there an expression that I could build that could add them together using the record numbers?

@ebledsoe22 Sure thing. You can just use the filter tool here if you already have record IDs. We can set the filter criteria to only filter for the range for records between 1 and 1.2 in this case. This will extract those RecordIDs? See attached ..

multifieldansweryxmd.yxmd

One way of doing this,

Input was:

Output is:

Workflow:

Hope this helps!!!

If you need the workflow let me know, I will explain the steps in detail as I am unable to save the workflow to attach here.

Many thanks

Shanker V

Hello. This is related to my previous Topic. I have data in a row that I need to multiply by data in a column. Should I use the Multi-field tool for this or is there another way to do it? See screenshot below. I need to multiply the ratio in row 2 by the amounts in the total column starting with row 3.

One way of doing this.

Row 3 is multiplied with Row 3 total * with corresponding row 2 column value.

Hope it helps!!!!!

Multiple Field Formula.yxmd