Could someone help me get the correct Multi-row formula?
I have a partial output as:
RecordID | Identifier | Amount |
18 | ||
54 | ||
64 | Y | |
65 | 37.5 | |
66 | 40.5 | |
67 | Y | |
68 | 22.5 | |
69 | 30.5 | |
74 | 53 | |
82 | ||
83 |
I want the sum of the above all values till the next "Y" in the [Identifier]
as:
RecordID | Identifier | Amount | Sum |
18 | |||
54 | |||
64 | Y | ||
65 | 37.5 | ||
66 | 40.5 | 78 | |
67 | Y | ||
68 | 22.5 | ||
69 | 30.5 | ||
74 | 53 | 108 | |
82 | |||
83 |
I am trying the multirow formula tool with the logic "If [Identifier] is null, add all above non null columns from [Amount] else null but I am unable to get it right.
Help is much appreciated.
Thanks
Solved! Go to Solution.
If I may make a suggestion - it's easier to assign a distinct identifier to Y using a multi-row formula than to create a running total. So instead of summing here - have your formula say
if [row-1:recordid]=null() then 1 elseif [identifier]="y" then [row-1:identifer]+1 else [row-1:identifier] endif
Note - the first part tests for the first record and creates an initial value of 1. It looks at a column field which will not have any nulls() in the previous row unless the row is the first row.
Now - once you have this identifier add a summarize and group by that new column and sum your data.
Is this the formula tool or the multi row formula tool where I will attempt this?
multi-row formula tool. Anytime you are looking at a previous row or future row - you use multi-row tool.
I think You mean
IF IsNull([Row-1:Identifier])
THEN 1
ELSEIF [Identifier] = "Y"
THEN [Row-1:AMOUNT] + 1
ELSE [Row-1:Identifier]
ENDIF
I am getting
RecordID | Identifier | Amount | New Field |
18 | |||
54 | 1 | ||
64 | Y | 1 | |
65 | 37.5 | Y | |
66 | 40.5 | 1 | |
67 | Y | 1 | |
68 | 22.5 | Y | |
69 | 30.5 | 1 | |
74 | 53 | 1 | |
82 | 1 | ||
83 | 1 |
Where am I going wrong?
There is no Row + 2 or Row - 2 else this can be an easier solution
Not sure what your fields were called when I wrote it out so I actually used "identifier" the second two times to mean the new field - sorry.
This is the proper formula:
if [row-1:recordid]=null() then 1 elseif [identifier]="y" then [Row-1:newfield]+1 else [Row-1:newfield] endif
[newfield] is the name you use for the new column.
Your [amount] column was non-numeric when I pasted it so the attached workflow does the multi-row properly and converts your [amount] field to a numeric field. Then it summarizes by different "y"s.