Referencing the cell above

@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

3 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!

2 Likes

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!

Hey everyone on the thread!

I saw a lot of activity in this thread lately, and since I was the one to create it, I felt some responsibility to answer this properly now that I know Coda much better.

I talked quite a bit about it in my recent livestream, starting at ~37:00, then going off track and returning to the topic at 1:17:00. Viewing at 1.5x or above is advised: I was very tired and was speaking too slowly.

1 Like

I have a use case whereby I need to reference the cell above but at the same time filter it.

image

I need to calculate the difference with previous month for each category.

hi @Hendrik_TnB ,

I guess one of your clients need to have this calculated :wink:

in two steps you have the solution. It starts with filtering out the on the Acc Name, in my example the article. This list of items FormulaMap() to get the delta

The second step is to show per row the value already see in this list of items using Slice() and Last() with an other filter that gives you the index per group of alike articles.

Let me know how it goes and if this makes sense to you.

Cheers Christiaan

5 Likes