Moving Average (6 Period)

[Jean-Claude_Siliato]

Hi,

I am trying to create a moving average column however having little success. Managing to reference the prior rows but having issues with the execution afterwards. (Potentially using the wrong formula to get the desired results.

Much appreciate any help!

For reference, here is a snip from Excel which explains what I am trying to do. Basically a 6 period moving average. Obviously for the first periods (1-5), the formula needs to be adjusted & unsure how to do this.

Thus far in Coda, this is what I have:

if(thisRow.[Row Index]=1,thisRow.[Per Unit],thisTable.Lookup(thisTable.[Row Index],thisRow.[Row Index]-1).[Per Unit]) + if(thisRow.[Row Index]=1,0,thisRow.[Per Unit])

Only adding the last row with current row.

[Christiaan_Huizer]

hi @Jean-Claude_Siliato , welcome to the community!

it is an interesting puzzle for which you need a filter to compare numbers. Are the periods related to dates and if so (I suppose they are) , can you show the dates you work with in Coda and relate them to the values you want to compare?

We then can have a look on how to help you.

I created this post, maybe it helps you out:

Moving average in Coda

Cheers, Christiaan

[Jean-Claude_Siliato]

Hi Christiaan,

Thanks for your response.
Yes, they do relate to dates (moth end)
I have seen that thread however remain unsure if it will work in this scenario. Perhaps some but not all?

I have created a coda doc with what I had thus far:

[Christiaan_Huizer]

hi @Jean-Claude_Siliato ,

I could not access the doc, but I copied the table and this is what I got

this is the code part, maybe the screenshots in my blog help you to see the logic.

thisTable.Sort(True(),sortBy: thisTable.Date).Filter(Date <= thisRow.Date).[Per Unit].Slice(-6).Average()

Cheers, Christiaan

[Jean-Claude_Siliato]

Thanks heaps Christiaan! This works a treat. I was not familiar with the slice function, making more sense now