Romain, this took me longer than it should have but I think this is what you want.
I added a 2nd tab to your spreadsheet, which is just a set of dates. That will be our x-axis value. The reason for it is that we need some way to compare an arrival or departure date to a baseline so we can tell how to count arrivals and departures.
The Validation worksheet shows the underlying math: I broke everything up into components so you can see how the parts fit together more easily.
I first calculated the # of arrivals and the # of departures as separate measures.
I then calculated the net of those 2 numbers (arrivals-departures). (Net Overnight Guests)
I used a LOOKUP function to get a starting count for the NEXT DAY: How many guests that stayed LAST NIGHT are still here TONIGHT. (Opening Count) (not STRICTLY necessary for the calculations I created but it helps illustrate how all the various functions work).
Finally, I did a RUNNING_SUM of the Net Overnight Guests calculation to (more or less) take the Opening Balance, add Arrivals and remove Departures to get to a Total Overnight Guests value.
That is illustrated on the Guests/Night worksheet.
I also gave you a dual-axis chart that shows the Arrivals and Departures as separate values.
There are a couple of problems with the way the calculations deal with future dates where we have no arrival/departure data, but for a restricted time window I think this does what you want.
Please feel free to ask questions - there's probably a simpler way to do this, but this is what I came up with!
Hi Mihai, thanks for your contribution. In this particular thread, I am looking to display the number of guests in-house per night, not the total number of nights per guest.
Thanks for your time and contribution, I am definitely a BIG step ahead thanks to your help.
I replicated your process with my whole data set but I am having issues with the following calculated field: "Opening Count" and "Total Overnight Guests".
Both return zero values for every single date in the Validation tab and Guests/Night tab. Would you know what's wrong?
Romain, that appears to be MY fault - I gave you an incorrect formula for the # of Departures calculation.
Please open that calculated field and in the formula replace "Arrival Date" with "Departure Date". That will fix the calculation.
I'm working on a simpler way to calculate this - will post if I figure it out!
I'm having doubts about what I sent you. Working on it...
We're getting there but indeed we're not yet arrived.
After correcting the calculated field, under the tab "Arrive/Depart", I have a far bigger amount of departure than arrival. This should not be the case.
Also, under the tab "Guests/Nights", after changing the measure to "Net Overnights Guests", I find myself with negative values. That must not be correct.
Again, thanks for your interest. I'm learning from you.
Yep, that's what I'm struggling with. Don't learn the wrong things from me!
I believe it's the way I did the join of the 2 Excel worksheets. I'm going to redo it a different way.
Romain, I'm stumped. I will continue to think about this but I'm going to have to set it aside for a little while.
My apologies - this is frustrating.
No worry, I appreciate your interest and help so far.
Let me know if you have more ideas.
4 of 4 people found this helpful
The attached is one way to do it. You can find other methods in FAQ: Open & Close Dates
First was a new sheet named Lookup added in your spreadsheet.
It has a column named Date that has a row for each date in 2017.
This new sheet (Lookup) is joined with your sheet (ResList) with dummy keys (1=1).
A filter is added so only relevant rows of the cross join (1=1) are included.
TABLEAU'S BUTTER ZONE IS STATS ON AGGREGATED ROWS
This new reference data, the cross join and the filter combined give you
one row per day of each guest's day between Arrival and Departure.
TECHNICALLY, METHOD 2 OF THE The Cross Join Collection WAS USED
A practical difference is that the cross join was done with Tableau's join dialogue.
The document also warns (see warning 2) against having a reference table with each day.
Actually, I think this method is perfect for hotel reservations, because these periods are so short.
Ps. This would be easier if Tableau had built-in support of 'one mark and/or row for each day (or other time unit) in a period, although the possibility to cross join with dummy keys already has made this much easier than in the old cross join days. You may influence Tableau to do so by up-voting following ideas:
Kettan, thank you for clearing up the confusion I caused!