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

    Help with Hotel Data

    Richard Chandler

      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
          Ankit Bansal

          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
            Richard Chandler

            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

             

            1.PNG

             

            Here is the arrival and departure for 4 records

             

            2.PNG

             

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

             

            3.PNG

             

            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
              Ankit Bansal

              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
                Ankit Bansal

                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
                  Richard Chandler

                  How do I get the attachment?

                  • 6. Re: Help with Hotel Data
                    Ankit Bansal

                    It is attached in my previous reply.