Multi Row Formula

[cfine]

I am trying to use a multi-row formula tool here to give me a difference column which will take sum of values for those line items which have the same description and Name.  I am having a hard time coming up with the correct formula.  I appreciate any help you can offer.

RecordID	Description	Name	Value	Difference
2	ABC	Cost	-1000
3	ABC	Cost	1250	250
2	ABC	Int	-1550
3	ABC	Int	870	-680

I have tried the following formula and it only gives me the value and not adding the 2 lines together.

ToNumber(If [Derivative]=[Row+1:Derivative]
AND [Name]=[Row+1:Name]
THEN ([Value]+[Row+1:Value])
ELSE NULL() ENDIF)

Thank you!

[OllieClarke · Accepted solution]

Hi @cfine I think a summarise tool is a better way to go than a multi-row formula

[LordNeilLord]

Hey @cfine 

You can make use of the grouping functions in the multirow here...group by Description and Name then you can just use the formula value+row-1:value 

[CharlieS]

Try this formula:

IF [Description]=[Row-1:Description] AND [Name]=[Row-1:Name] THEN [Value]+[Row-1:Value]
ELSE null() ENDIF

[OllieClarke]

@cfine If you only want this value on the final row, then take the max recordID as well and use that in the join too. This approach works if you have multiple or varying rows per name/description, whereas a multi-row formula would be less dynamic

[LordNeilLord]

@OllieClarke  = multi-row hater

[Jonathan-Sherman]

Hi @cfine,

Is this what you're looking to achieve? I've simply summarised by description and name and taken sum of values.

If this solves your question please mark the answer as correct, if not let me know! I've attached my workflow for you to download if needed.

Regards,

Jonathan

[Jonathan-Sherman]

@LordNeilLord  @OllieClarke looks like i was a little late on this one!

[OllieClarke]

@LordNeilLord I love a multi-row, I'm just saying what happens if your data's like this:

RecordId	Name	Description	Value
1	ABC	Cost	-1000
2	ABC	Cost	1250
1	ABC	Int	-1550
2	ABC	Int	870
3	ABC	Int	2500