Referencing the cell above

To find the visual position of the row:

thisTable.Find(thisRow)

You can reference the above with:

thisTable.Find(thisRow)-1

Not true, because if the table is grouped, sorted, or filtered, this will no longer return the visual position but the position in the original dataset.

This post of mine is a very old post. Now I discourage simply referencing the previous row and encourage pulling a relevant previous row through a thought out formula. It’s more often than not that the table for e.g. financial calculations would have entries for multiple users (in any order). In that case you definitely don’t want just the previous row — instead you want to filter for e.g. same user previous month entry.

2 Likes

Hi, just checked, so we do have the “thisRow” reference and after reading about, it really feels “something is missing” when you look for previous and next. Obviously, there is a serious disadvantage of this formulas because you can end up with some performance problems needing to process almost everything when some related column changes.

I suggest, this easy formula:

  • nextrow(table,rowference,filter expression,sort column 1, asc/desc, sort column 2,…),
  • or, table.nextrow()
    then you can recalculate only if something of that filter partition (works like partition by in SQL) changes plus another optimizations.

Table=table to get next row from.
Row Reference=default thisrow, or select any unique row from another table.
Also, to simplify even more, you can enable an option to just get next visible row and make filters and sort optional (so you can just filter and sort the table).

That is almost equal to this formula assisted by just a rowid column to overcome duplicates:

ThisTable.Filter(
CurrentValue.Category=thisRow.Category)
.Sort(True(),ThisTable.Item)
.Sort(True(),ThisTable.Date)
.Filter((Concatenate(CurrentValue.Date.ToNumber(),CurrentValue.Item)>=
Concatenate( thisRow.Date.ToNumber(),thisRow.Item)
&&(CurrentValue.RowId!=thisRow.RowId))).First()

You can play around with this example that contains partitioning, duplicates and previous and next row formulas examples:

1 Like

@Paul_Danyliuk, @shishir

First of all thanks a lot for your contributions on this subject. It took me a while to try to understand all the work you did and I am not even sure I do fully understand it as I have no coding back ground.

Anyway I used your input to work out a different solution for two scenarios you solved with Previous Row Logic

Previous Row - Sales of days before
To calculate the cumul of something like sales (first day, second day, third day etc)

I used a “coordinate based” logic whereby I use

  • first row is 1.1
  • second row (including the first row) is 1.2
  • third row (including the first and the second) is 1.3

And so on.

This logic uses Lookup and Slice, whereby the starting point always is “1” The RowIndex is the variable.

Lookup(thisTable,RowIndex,RowIndex).sales.slice(1,thisRow.RowIndex).Sum()

The interest rates part 1
In this scenario I used mainly the Power() function. The RowIndex is used as the year. No need for a Previous Row Logic

Product(Power(1+thisRow.Percentage,thisRow.RowIndex),thisRow.Amount)

The interest rates part 2
This is a variation that is based again on the Power() logic (thanks @Paul_Danyliuk using this formula in your code) and instead of using the RowIndex, we need a first year - I used Min() - and the rest follows.

Product(Power(1+thisRow.Percentage,thisRow.[Delta Year]),thisRow.Deposit)

If you start with a year, is is directly a full year, that explains the + 1

In sum, I guess we need the Previous Row logic once in a while (like in my sales example), but not when we calculate interest, that is an optical illusion :wink:

Best, Christiaan

2 Likes

Hi
I have been referred to this topic by Coda support but am completely lost. My small business of 21 years is struggling since Covid lockdowns and I need a Daily Cash Flow involving 2 banks and cash. I have summarised the month by days by bank but now need a running consolidated daily total. I am unbale to reference the summarised totals to the rows at the top of the table. Any ideas.
Coda | A new doc for teams..
Thanks
Mike

Hi @Michael_Brits check out my answer to this post:

1 Like

@Christiaan_Huizer I am trying something similar but it doesn’t seem to work. I am trying to calculate the cumulative sum (in the last column)


of new employees in the screen shot attached. Any ideas why? Thank you!

@Ryan_Chadha , can you post a second screenshot that shows the full picture and the code separate like

thisTable.Filter(RowIndex.contains(thisRow.RowIndex).Slice(1,thisRow.RowIndex).Sum()

thx, cheers, christiaan

1 Like

Sure. This is the formula:

Screenshot 2021-07-14 at 4.17.32 PM

Lookup(thisTable,thisTable.[Row Index],thisTable.[Row Index]).[New Employees].slice(1, thisRow.[Row Index]).Sum()

This is the table:

what about this :slight_smile:

the logic is that instead of working with the assumed thisRow logic, you ask for all the numbers in the list via thisTable and once you have all the numbers, you slice that list and sum the results.

thisTable.[New Employees].Slice(1,thisRow.RowIndex).Sum()

1 Like

Excellent. Thank you so much!! Also going to try the interest example you gave, so I might be back :slight_smile:

my pleasure, you noticed maybe the shorter solution than previously written. Over time I improved my understanding of coding Coda. Some of my learnings in this regard you find here : Coda.io and Previous Rows. Al Chen wrote : | by Christiaan Huizer | Medium

I still have to publish a blog over interest calculations, the moment I have a client in need of such a solution I feel motivated to dive into the subject again :wink:

1 Like

That’s great! :grinning:

When you have time, can you help me with this too:

I am trying to calculate the change in interest from the previous year, so total interest in current row - total interest in previous row. What should the formula be?

SwitchIf(thisRow.[Row index]=1, 0, thisRow.[Row index]>1, thisRow.[Total interest])

you can do this in various ways, the key is time every time the RowIndex variation named as years (generated via thisTable.Find(thisRow). I splited the steps to make it easier to follow the logic I deployed.

step 01
sum(1,thisRow.Percentage).Power(thisRow.Years)

step 02
thisRow.Amount * Sum(1,thisRow.Percentage).Power(thisRow.Years)

step 03
thisRow.Amount * Sum(1,thisRow.Percentage).Power(thisRow.Years) - thisRow.Amount * Sum(1,thisRow.Percentage).Power(thisRow.Years -1)

step 04
thisTable.[step 03].Slice(1,thisRow.Years).Sum()

the software encouraged me to involve others in the discussion, maybe @Federico.Stefanato wants to add something ? :wink:

2 Likes

Hi @Christiaan_Huizer and @Ryan_Chadha,

I think this approach works really well.

If you want to further investigate - and for the sake of optimisation - I did this exercise before:

As you can see, there are two pages.
One with a dynamic retrieval of the previous row (to calculate the interest), as you suggested.
The subpage the very same but with a is purely generated one-off timeline.

Basically, all the computation is performed in the button logic just once.

Enjoy!

1 Like

thanks, that is a nice way to solve this kind of problems.

below an image that show you with one formula (a bit like in the button) you can have the same result. I kept it simple because the virtual index corresponds with the years, but of cours you can adapt the sequence logic if needed.

2 Likes

@Christiaan_Huizer This is amazing. Thank you so much!

I also found a couple of articles you’ve written on Medium so will go through those tomorrow.

This is great @Federico.Stefanato! I am essentially looking to make some time value of money calculations and then plot them on a chart.

So this and all the help I’ve gotten from @Christiaan_Huizer is really very helpful!

I hope to have something ready soon and will share the doc with you both :smiley:

2 Likes

sometimes it is even simpler than previously thought, a bit silly I did not see that directly.

Looking forward seeing your doc!