How to get `Nth` to return `Blank` if index is not found

[Connor_McCormick]

Right now, Nth returns an error if the provided index is not found.

E.g. if my table has three rows, and I write the formula:

[My Table].Nth(5)

I will get this result:

However, if I’m trying to write a running total with Nth I could approach it like:

thisTable.Nth(thisTable.Find(thisRow) - 1).ifBlank(startingValue) + thisRow.Amount

You would think this works, but since Table.Nth(i) returns an error when i > Table.Count()—rather than returning Blank—it breaks. If it returned Blank not only could this formula work, but it would also likely allow more efficient lookups, e.g.

Running Total (Sum, Average, Median, etc)

I think there’s a way to do this more efficiently by using an index-lookup approach rather than a find approach. Make a column called Order that converts your datetime objects into a unique integer. Then Running Total formula can just be thisRow.Value + thisTable.Sort(true, Order).Nth(thisRow.Order - 1).Value

Is there an elegant way to get around this?

[BODHI_SCHNEIDER]

I’m not sure about this on tables, but this formula could work:

 List("","cats","dogs").Nth("1")

[Connor_McCormick]

 List("","cats","dogs").Nth(4) 

Won’t though, right?

[BODHI_SCHNEIDER]

Yep. Are you using a control to determine the value of the nth?

[BODHI_SCHNEIDER]

Here is a formula that might work:

If([slider 1]>[Table 1].Count(), "",[Table 1].Nth([slider 1]))

Hope this helps!

[Connor_McCormick]

Maybe I’m not making sense.

The goal is to make a running total of a column.

I want to use thisTable.Nth() to look up the previous row. However, the first row will not have a previous row. I’d like to know how to access that previous row

[BODHI_SCHNEIDER]

You might want to look at this topic:

Referencing the cell above

Sometimes you need to implement iterative calculation where a value in the next row depends on the value in the previous row (e.g., interest calculator). To do this in Excel / Sheets, you simply reference the cell above by specifying the row as current row index minus 1. In Coda, though, there is no concept of “cell reference” or a ready-to-use row index, and if you try to enter the formula for Value column as =Value.Nth(…), you will get a circular reference error. Solution? — Construct someth…

Although it solves your problem, the rowid is not reliable in the situatuion, so you might have to tinker around with the formula.

If you have any other questions, you can ask them to me.

[Connor_McCormick]

Yeah, I know how to do a running total with thisRow.RowId() but I want to do it with Nth, that’s why I’m asking if there is a way to get Nth to return blank (not error) if it fails a lookup.

[BODHI_SCHNEIDER]

I’ve been making a doc with that, here:

(you’re probably going to want to hide the rowid column., also, you should use a sort.)

I hope this helps!

[Jean_Pierre_Traets]

Dear @BODHI_SCHNEIDER,

Thanks sharing a sample, would you mind to update the sharing setting to " everybody with the link", to become visible to the community?

Always eager to learn

[BODHI_SCHNEIDER]

Sure @Jean_Pierre_Traets!

Just PLEASE don’t delete anything, I couldn’t find play so I put it on edit.

[Pch]

@BODHI_SCHNEIDER

Just PLEASE don’t delete anything, I couldn’t find play so I put it on edit.

The play mode is in the Embed option

So far, my Test docs are still in “can view” .

[Ryan_Martens1]

Hi Connor, why would the index ever be out of range if you are finding thisRow in thisTable? Except for the first row, I suppose, which has no previous.

But maybe this is what you are looking for? But you are right, it is not as elegant as Nth() returning a blank value rather than an error.

WithName(Find(thisRow,thisTable), index,
   If(index=1, thisRow.Amount,
      thisRow.Amount+thisTable.Nth(index-1).[Running Total])
 )

If for whatever reason I thought that index might be higher or lower than the range, I would have to include that in my if statement. Eg, If(index<=1 || index>Count(thisTable), ...

Note that this index is the manual order of the rows and does not reflect either RowID or any filter/sort AFAIK.

As per OP’s original intent, can anyone confirm whether this is actually more efficient than the filter/slice version of the running total formula? Does it make any difference?

[cnr]

Yeah, this specific lookup is likely not more efficient. When I’m back at my computer I’ll have to look back at what I thought would be more efficient.

Yes, an if statement solves the problem at the price of elegance.

[Ryan_Martens1]

So apparently it is working now. In my memory, this formula brought up an error before, and now it still has an error but continues the calculation.

thisRow.Amount+thisTable.Nth(Find(thisRow,thisTable)-1).[Running Total]

The inelegant if statement is gone

(Updated my doc above.)

So @cnr you are telling me that my three formulae in this doc are equally efficient?

[Christiaan_Huizer]

@Ryan_Martens1

thisRow.Amount+thisTable.Nth(Find(thisRow,thisTable)-1).[Running Total]

For my understanding, The Running Total is the delta between N and N+1 ?

if that is the case an alternative might be:

thisTable.[Running Total].Slice(1,Find(thisRow,thisTable)).Sum()`

best, Christiaan

[cnr]

Still haven’t had a chance to play with this. I’ll get back to you

But yes, it is just the first row that returns the error, as you note

@Ryan_Martens1

Except for the first row, I suppose, which has no previous.

[Ryan_Martens1]

Yes. Assuming “Running Total” is the name of the column with the raw amounts (ie, not a self-reference) - in my table the column called “Amount”.

But yes, that’s a more elegant solution