Simplify Redundant Expressions

[Alyssa_Gono]

As a Coda newbie, I found writing formulas complicated. Example, I have the formula below. It has redundant embedded expressions.

If(thisRow.[List of Effective Dates].Count() <= 1,‘NULL’,WithName(WithName(Sequence(2,thisRow.[List of Effective Dates].count()).FormulaMap(CurrentValue.WithName(i,(thisRow.[List of Effective Dates].Nth(i-1).ToNumber() - thisRow.[List of Effective Dates].Nth(i).ToNumber())*-1)).Min(),minDate,Sequence(2,thisRow.[List of Effective Dates].Count()).FormulaMap(CurrentValue.WithName(i,(thisRow.[List of Effective Dates].Nth(i-1).ToNumber() - thisRow.[List of Effective Dates].Nth(i).ToNumber()) * -1)).Find(minDate)),minDateIndex,If((thisRow.[List of Effective Dates].Nth(minDateIndex + 1).Year()-thisRow.[List of Effective Dates].Nth(mindateIndex).Year()!=0),thisRow.[List of Effective Dates].Nth(minDateIndex + 1).Year() - thisRow.[List of Effective Dates].Nth(minDateIndex).Year(),WithName((thisRow.[List of Effective Dates].Nth(minDateIndex + 1).ToNumber() - thisRow.[List of Effective Dates].Nth(minDateIndex).ToNumber())/365,lessThanAYear,[Less Than One Year Gap].Nth(WithName([Less Than One Year Gap].FormulaMap(AbsoluteValue(CurrentValue-lessThanAYear)).Min(),Minimum ,[Less Than One Year Gap].FormulaMap(AbsoluteValue(CurrentValue-lessThanAYear)).Find(Minimum) ))))))

Is there any way, I can simplify it? Its very hard to maintain and debug.

[Agile_Dynamics]

@Alyssa_Gono

you should break down your logic across several columns. so the redundant code is used once for an intermediate result (column) which can be referenced by the main formula.

you can also use the WithName() function

expression.WithName(X, … )

this will execute the ‘expression’ and assign the result to X so you can refer to X several times in the ‘…’ code that follows.

the scope of X is strictly limited to the code between the parentheses.

these two techniques should resolve your issues

max