Coda prices for accommodations
while dealing with overlap and gaps
We got a rather difficult puzzle to solve on Tuesday, March 5, 2024. The user had tried a few things, even copied code from other posts but got stuck. That is understandable seen the complexity he faced.
The final solution was deliverd by the great Coda connaisseur Joostmineur. In this blog I explain his contribution in greater detail, while I rephrase his code.
What is it about?
There are periods and each period is related to a price per night. That is a good start. Next we notice that we have periods with overlap, that is unusal and complicates the matter a bit because we get multiple outcomes and we have to decide which one to take. Code wise, this is still rather straightforward. Last but not least, we got renting dates not covered by the the defined periods and this makes it a difficult puzzle. As you may understand it is a serious challenge when you are not an experienced Coda user yet (and even then).
Step by step
I had a look at how the initial code was constructed and I noticed practices I’d like to set up differently and so I reworked the tables a bit as you see.
We have thus tables and in the column Joost we have the solution based on the periods living in the table DB Prices.
Step 01: defining the periods
We need to evaluate each date and that requires a list of dates we get via a sequence. This function interprets dates as numbers. Since we talk about nights we need all dates, but the last one. We name the outcome: TheDates.
Next we check if the startdate in the table DB Prices is smaller or equal to each of the dates and we check if the end date is greater than any of the dates to get the periods that apply per date. The result of this filter we named outcome and it looks like below. I used the color red to point out that sometimes we have two values. That is the overlap we noticed earlier.
Step 02: dealing with overlap
Would we only have had this overlap issue, the code would quasi be complete, but it all starts here since we also have dates not covered by a period. First how we handle this part overlap part.
Sequence(thisRow.arrival, thisRow.departure -1).ForEach(CurrentValue.WithName(TheDates,
[DB Prices].Filter(
startDate <= TheDates and
endDate > TheDates
).WithName(outcome,
SwitchIf(
outcome.IsNotBlank(),
outcome.price.Max()))))Dates are numbers and we compare higher with lower numbers via logical operators.
Would we not have had the empty periods, the function would have been the filter ending on price.Max(), but we have empty periods so we deal with two scenarios. By the way, we use the max value because Stefan wants always to charge the highest possible price per night. That is a commercial choice we translated into code. In comparable situations you often see First().
Step 03: getting the values for dates without a reference
To show you what we are after I added a visual indication and that is possible because of the SwitchIf(), it is the fall back option. When the argument is false, this is what you see.
Most mortals would be able to get the code so far. It is a pattern that has been documented in the community already. However to finish the solution you need a bit extra.
We need to replace the — in the outcome based on the business rule that it should be the preceding value when the outcome is not blank. The number we need depends thus on the last date value of the previous period and per period we need to get the max price (in case there are many). Again, this only happens when we are in the fall back sitution.
In the community you can see that Joostmineur wrote his snippet in one part (a filter inside a filter) but I decided to split it. We first look for the last date per period, that explains the name I apply and second we use this date value to get the max price per endDate in the second filter. We are almost there, one little function to add.
Here we are, Joostmineur created a wonderful smart solution that can easily be applied in larger tables.
Conclusion
This was a difficult excercise and I hope that by reading the steps you gain a better understanding of the power of the Coda formula language. It is powerful to the extend that we get outcomes without helper tables full of dates.
I wrote this blog on Tuesday evening. On Wednesday morning I noticed that Stefan — in his own doc — created an extra column with the code you see below to show that some periods lack date values:
If(
Sequence(thisRow.Arrive, thisRow.Leave - 1)
.FormulaMap(
WithName(
CurrentValue,
Date,
If(
Prices
.Filter(
Date >= [Valid from] && Date <= [Valid until]
)
.Count() =
0,
Date,
""
)
)
)
.Filter(
CurrentValue.IsNotBlank()
)
.Count() >
0,
"Missing prices for some of the dates",
Sequence(thisRow.Arrive, thisRow.Leave - 1)
.FormulaMap(
Prices
.Filter(
CurrentValue >= [Valid from] &&
CurrentValue <= [Valid until]
)
.Price
.Max()
)
.Sum()
)It is about this: “Missing prices for some of the dates”.
I believe this can be achieved in a simpler way:
The conditional format can be attributed to the display column as in my example and you can hide the column with the ‘-’ .
Anyway, in case you need serious Coda work done, reach out to the great Coda connaisseur Joostmineur.
Hopefully, this post has shed some light on this common Coda challenge and empowered you to create more interactive and insightful data experiences. On a personal note, creating these in-depth posts takes a lot of time and effort. While I love sharing my knowledge, a little support goes a long way. If you found this helpful, what about a donation or sharing this post with your fellow Coda enthusiasts? Every bit of encouragement helps fuel the next blockbusters!
My name is Christiaan, and I regularly blog about Coda. While this article is free, my professional services (including consultations) are not, but I’m always happy to chat and explore potential solutions. You can find my free contributions in the Coda Community and on X. The Coda Community is a fantastic resource for free insights, especially when you share a sample doc.
