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. RecordIDDescriptionNameValueDifference2ABCCost-1000 3ABCCost12502502ABCInt-1550 3ABCInt870-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!

Multi Row Formula

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!

Help

Accepted answers

OllieClarke

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

All comments

OllieClarke

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

20191108-MultiRowFormula.yxmd

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

20191108 - SummariseValue.yxmd

Jonathan-Sherman

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

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703