@Johg_Ananda Okay, here’s a more expanded reply. I’d illustrate it with a formula I wrote yesterday:
[DB Scenario metric uplifts].Filter(
Scenario = thisRow
AND [Value driver].IsNotBlank()
AND thisRow.[Select value drivers].Contains([Value driver]).Not()
thisRow.[Select value drivers].IsNotBlank() AND [DB Scenario metric uplifts].Filter(Scenario = thisRow AND [Value driver].IsBlank()).Count() = 0,
[DB Scenario metric uplifts].AddRow(
Ramping, [Template ramping].Nth(CurrentValue + 1)
thisRow.[Select value drivers].IsBlank(),
thisRow.[Select value drivers].FormulaMap(
thisRow.ModifyRows(thisRow.[_Current value driver], CurrentValue),
thisRow.[_Current value driver].[Metrics Impacted].FormulaMap(
thisRow.[_Current metric impact], CurrentValue
thisRow.[_Add current metric impact]
As I said above, I use best judgement when deciding whether to break down into separate lines or not. The goal is formula readability in the constraints of a pretty narrow window. If breaking down into separate lines makes reading the formula harder and not easier, it would make sense to not do that, right?
Example of that:
DeleteRows formula, I’m breaking down the conditional in Filter into separate lines:
This is what I’m usually doing BTW with long boolean expressions with lots of
However, further down I’m not breaking a similar long filter expression into separate lines:
This is easier for me to read because I know that to jump to the
clause, I don’t need to scan the formula line by line, but can simply jump the block of text that overflows, right to the next indented line. Whereas if I broke down the expression like this:
it would require me to scan each line, looking for a comma.
My rules of thumb:
RunActions() — always break down and indent, each action starting on a new line. If actions are too long, consider adding empty lines between actions.
FormulaMap() — always break down and indent.
Filter() — keep inline if the condition is simple (only one operation e.g.
X = Y or
X.Contains(Y).Not()) or breaking down would only make overall readability worse (e.g. when surrounded by lots of other code), otherwise break down and indent.
If() — keep inline if the condition is simple and the true/false values are simple as well, otherwise break down and indent.
Switch() — always break down and indent. Write the expression on a separate line, then case and value pairs each on the same line if the case is just a value. Otherwise consider using
SwitchIf() — always break down and indent. Write expressions and calculations on separate lines. Consider adding empty lines between expression/calculation pairs if either part gets broken down into multiple lines itself:
thisRow.[New lines in this row].Not(),
thisRow.[Cohort type] = "Adds",
Metric = [New Lines/Services]
AND Year = thisRow.[Year index]
Metric = Winbacks
AND Year = thisRow.[Year index]
ModifyRows() — only consider keeping inline if you’re setting one column->value pair, otherwise break down into separate lines, with column and its value being on the same line:
Also if one of the paths in an
If() is long (i.e., takes more lines than a formula editor can display), and the condition is easy to swap (e.g. just replacing
.IsNotBlank(), consider writing a shorter path, e.g.
_Noop(), first, so that you can see it while still seeing the condition: