6 Replies Latest reply on Jul 30, 2018 7:44 AM by Ankit Bansal

# Help with Hotel Data

I am building a Tableau dashboard for a hotel. This dashboard rolls up revenue by month. The issues I am having is as follows;

I have a list of check in and out dates along with daily rates.

Adam Smith Check in 01/31/2018 Check Out 02/02/2018 Daily Rate: \$100

So the guest stays the night of 1/31 and the night of 2/1 at \$100 each night

Right now this is what I get on my Dashbaord

• Jan Revenue = \$200
• Feb Revenue = \$0

But since one day is in Jan and one day is in Feb I want to display as

• Jan Revenue = \$100
• Feb Revenue = \$100

I am using the check in date as the pivot but not sure how to use both dates to get the data I am looking for. I have done it in excel like this;

=COUNTIFS(rawnocxl!\$A:\$A,"<="&A2,rawnocxl!\$B:\$B,">"&A2)
Where rawnocxl A is the check in date and B is the check out date. A2 is the date I am looking up. In this case I am just counting the number of hotel rooms occupied on any given night.

Any help you can give is appreciated! Thank you

• ###### 1. Re: Help with Hotel Data

Richard,

One method of doing it is by creating a date table/excel which will have all the dates for all the months you need data on dashboard like:

AS_OF_DATE

01-JAN-2018

02-JAN-2018

03-JAN-2018

.

.

31-JAN-2018

01-FEB-2018

02-FEB-2018

03-FEB-2018

.

.

28-FEB-2018

Now join this table with your original table with on (1=1), using "create join calculation" (put just 1 in both calculation).

Now create a calculation column as:

AS_OF_DATE >= check_in_date and AS_OF_DATE < check_out_date

Put this field on filter shelf and choose TRUE. After that your data will look like this:

NAME                  Check in date      Check Out      Daily Rate     AS_OF_DATE

Adam Smith      01/31/2018        02/02/2018       \$100              01/31/2018

Adam Smith      01/31/2018        02/02/2018       \$100              02/01/2018

Now use this AS_OF_DATE for your monthly roll up.

Hope it Helps.

Note : If your source of data is a database (not excel or CSV) then JOIN only you can make with below condition

AS_OF_DATE >= check_in_date and AS_OF_DATE < check_out_date

and then you need not create field to put in filter shelf. But for excel >,< joins are not available.

Hope it helps.

• ###### 2. Re: Help with Hotel Data

Thank you so much for your reply.

I created a new sheet inside the workbook with every day of the year listed. Jan 1 - Dec 31.  I created a calculated join field as described

my column names are as follows;

check in = Arrival

check out = Departure

Where does the As of Date field come in? is this the new calculated column I am creating?

Take a look

Here is the arrival and departure for 4 records

And here is the new column from the date list workbook (with all dates) for those same 4 records

It seems most are pulling in 1/2/2018 or 1/3/2018 in this new column.  This is where I am getting confused.

I did create the calculated column as instructed and filtered by TRUE on my dashboard but suddenly all of my revenue numbers increased 1000%!

• ###### 3. Re: Help with Hotel Data

AS_OF_DATE is the column name of the new worksheet you have created(Dates in your case) , It should have unique values of all the dates. For example if you have 1 year data then it should have 365 entries.

Now after joining your data (1=1) you should create a calculated field as:

Dates>=arrival and dates<departure

and put it on filter and select TRUE.

If you still not getting correct data, Then can you share the workbook(with sample data) or at least the snap of your calculated field formula (created for True/False)

• ###### 4. Re: Help with Hotel Data

Richard,

I have created the sheet with sample data. In my sheet 2 i have just added 3 dates just for illustration.

Please find attached.

• ###### 5. Re: Help with Hotel Data

How do I get the attachment?

• ###### 6. Re: Help with Hotel Data

It is attached in my previous reply.