1 2 Previous Next 16 Replies Latest reply on Jul 10, 2017 9:59 AM by kettan

# How to display a daily number of guests based on the start and end dates of their stays at my hotel ?

Hi !

From my hotel reservation list, I have the following three columns:

- guest name

- arrival date

- departure date

I want to display on a vertical bar chart, where every bar would represent a day, the number of guests that have stayed at my hotel.

If I use the "arrival date" or the "departure date" in the Column field, all I can have is the number of guests sorted by arrival or departure date. I want to know the same of guests staying in every night.

How can I do that? See the workbook attached.

• ###### 1. Re: How to display a daily number of members based on the start and end dates of memberships ?

You mean calculate the number of days of residence: DATEDIFF('day',[Arrival Date],[Departure Date]) and plot that as bars per guest?

• ###### 2. Re: How to display a daily number of members based on the start and end dates of memberships ?

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!

Bonne chance!

• ###### 3. Re: How to display a daily number of members based on the start and end dates of memberships ?

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.

• ###### 4. Re: How to display a daily number of members based on the start and end dates of memberships ?

Hi Michael,

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?

Thanks,

Romain

• ###### 5. Re: How to display a daily number of members based on the start and end dates of memberships ?

Romain, I'll look at this shortly. We'll find the right answer!

Get Outlook for iOS<https://aka.ms/o0ukef>

• ###### 6. Re: How to display a daily number of members based on the start and end dates of memberships ?

Hi Michael, here the packaged workbook as an example of where I'm stuck at.

Hope this helps.

Romain

• ###### 7. Re: How to display a daily number of members based on the start and end dates of memberships ?

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!

• ###### 8. Re: How to display a daily number of members based on the start and end dates of memberships ?

I'm having doubts about what I sent you.  Working on it...

• ###### 9. Re: How to display a daily number of members based on the start and end dates of memberships ?

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.

Romain

• ###### 10. Re: How to display a daily number of members based on the start and end dates of memberships ?

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.

• ###### 11. Re: How to display a daily number of members based on the start and end dates of memberships ?

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.

• ###### 12. Re: How to display a daily number of members based on the start and end dates of memberships ?

No worry, I appreciate your interest and help so far.

Let me know if you have more ideas.

Regards

• ###### 13. Re: How to display a daily number of members based on the start and end dates of memberships ?

The attached is one way to do it. You can find other methods in FAQ:  Open & Close Dates

DATA

It has a column named Date that has a row for each date in 2017.

CROSS JOIN

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:

4 of 4 people found this helpful
• ###### 14. Re: How to display a daily number of members based on the start and end dates of memberships ?

Kettan, thank you for clearing up the confusion I caused!

1 2 Previous Next