Display is a concatenation of Year and Week Number.
What I need to do now is calculate a four week projected value, which is the sum of the next four weeks’ invoices.
I have one approach which partly works, based on a formulated field that filters via the week number, but it fails at the end of the year because there is no week number 53, 54, etc..:
Sum(thisTable.Filter(CurrentValue.Year = thisRow.Year).Filter(CurrentValue.[Week Number] >= thisRow.[Week Number]).Filter(CurrentValue.[Week Number] < thisRow.[Week Number] + 4).[Invoice Total for the Week])
hi @Daniel_Hollands1 , it has been a while, good to see you back again.
first I assumed you need this (taking the last 4 weeks as a projection), but reading again I noticed you want the sum of the next four weeks’ invoice (which I don’t understand)
thisTable.Filter([Week Number] <= thisRow.[Week Number]and Year.Contains(thisTable.Year)).Slice(-4).[Invoice Total for the Week].Sum()
so this is the one you may need:
thisTable.Filter([Week Number] >= thisRow.[Week Number]and Year.Contains(thisTable.Year)).Slice(4).[Invoice Total for the Week].Sum()
The idea is to have a weekly scorecard metric, which is a forward-looking projection of what we expect to invoice over the four weeks following any given week. This is because the amount we invoice every week can fluctuate, but it should be relatively balanced over four weeks.
Unfortunately, your example doesn’t provide what I need. I’ve added a sample document below and implemented your example into the Four Weeks Projected B column.
My Four Weeks Projected A example works for most of the year, but fails in the last couple of weeks because it can’t recognise the end of the year and the start of the next. This is why I was hoping to simply select the current row, and a number of rows after it to do the calculation.
Finding “the next X rows” in Coda is not a simple exercise, for reasons which I explain the page embedded below. It also includes some examples on how to actually achieve that.
It might be simpler to amend @Christiaan_Huizer 's example to put it inside a set of If() statements so that you explicitly cover the situations for weeks 49, 50, 51 and 52 . If you do not delete older entries, remember that you will also need to cover the year. If not, once you have more than 52 weeks in the table, you will start running into duplicates.
As for my issue, I’ve solved it another way. I was making it more complicated than it needed to be, and instead am simply querying based on date ranges, keyed against a week commencing date using something similar to the following:
