I started this case thinking it would be straightforward to calculate. However, after what feels like an eternity of trial and error (and maybe a bit of imaginary beard stroking), I’ve hit a roadblock in calculating the total price for accommodation periods.
The case:
I’ve got 2 tables:
Table Prices: A list of prices applicable to different periods.
Table Accommodations: Information on accommodations for various periods.
The goal
Calculate the total price for an accommodation period, accounting for different prices within that timeframe.
This I proudly achieved, … kind of.
Where I need your expertise
Things get too scary for my imaginary beard when:
EDIT: a) Two prices have overlapping active periods (1-2 days). In such cases, the total should consider the price that has a higher price for the overlapping period.
b) There are accommodation period days with no corresponding valid prices in the Prices table. For these days, I aim to apply the price that concluded just before the blank (gap) days. This logic should extend to multiple periods within the accommodation.
Hmmm, that is kind of strange, because in your earlier text you said:
a) Two prices have overlapping active periods (1-2 days). In such cases, the total should consider only the price that starts earlier and transition to the second price only when the first one concludes.
And is this only limited to a max of 2 days? In your example the overlap is 5 days and in your correct answer you use the lower price (15 x 50).
I am willing to look at your problem, but it should be clear what has to be accomplished.
I am afraid I don’t understand how your pack operates in this context, would you mind clarifying it? second the use case is a complex one because one date can have two prices, so there is overlap. @joost_mineur asked for clarification in this context.
A standard set up to define prices for goods & services would relate prices to periods without overlap: one day and one price. Overlap requires an additional business rule, because you can have more than one overlap in this logic, maybe 3 or 4.
Using the pack, I generated a new Daily Prices table with one row for each date from min(Valid From) until max (Valid Until)
Each row has a calculated column with the price for that day based on @joost_mineur’s comment about the overlapping: If there is one price for that day, use that. If there are two prices, use the more expensive (or least expensive). If there are NO prices for a date, use the most recent price.
In essence, my point to demonstrate that the price column on the Daily Prices table contains all the logic for calculating the price for a specific date. It’s much less complicated than trying to generate this logic from the date range.
Troy, thanks for the contribution. I am trying to understand how the pack would act with dynamically changing data for hundreds of accommodations and prices
I think your “Correct answer should be” is not correct. Just looking at Person 1, checking in on Jan 1, the rate would be 50 euro. Up to Jan 9, the rate would stay the same, so that is 450 euro.
On Jan 10, the rate becomes 100 euro.
Etc.
So the totals are a bit different from what you think.
The formula that works is like below. It’s not a beauty, but what you are asking is not very standard.
I requested edit access to your doc and I will add a column with the correct totals (I think) upon granted access.
edit (2) - corrected formula (added one more max() to catch exceptional situations)
Based on what I’ve learned, I created an additional scenario that displays a message when not all dates have corresponding prices. In the event of relevant prices being added to the Price table, the formula calculates the total by considering all the applicable prices. In cases of overlapping prices, the formula prioritizes the one with the higher price.
Both formulas take over a second to calculate. For my real doc with hundreds of rows and thousands of prices, I consider triggering the calculation with a button.
I revised the demo I posted earlier based on @joost_mineur and your formulas.
This is the Price Table that gives you calculated price for each day. Here’s the benefits to using a price table approach rather than a single formula:
You can validate the logic to make sure the right price is being used for the right days
This gives you a simple price sheet where you can lookup the price for any given day
You could export the price sheet to a spreadsheet, DB, etc.
Thanks, master Joost, for the solution!
Both formulas take over a second to calculate. For my real doc with hundreds of rows and thousands of prices, I consider triggering the calculation with a button.