How to properly set up pivot table for hotel data set?

Hi guys,

I received a massive hotel data set (booking sources, cancellations, cancellation dates, arrival dates, departure dates, rate sum etc. included) with booking data from 2012-2019.
Now, I would like to know how you make a pivot table where I can see for which arrival date, which 'source' made a booking, how many days in advance (booking date). How should I set up my pivot table (Filters, Rows, Colyms, Values) to figure this out?
I guess these three variables have to be included in my pivot table:
- Arrival date
- Source
- Booking date

Problem is; I don't know how to set it up correctly.. Sorry for my noobiness
Many thanks in advance!

Hello,

you need to add a column to your data where you calculate Arrival date minus Booking date to get the days in advance.

Then you can build a pivot table with Source, Booking Date and Advance all in the rows area. The problem is that you will have many individual combinations of Source, booking date and Advance, so a pivot table won't be of much help.

2019-01-07_15-16-24.png

Before building the pivot table you need to formulate the question that the pivot table should answer. What do you want to know?

Suggestion: Pull the source and the Advance columns into the rows area, and the source again into the values area for a count. Then click an advance number in the pivot table, right-click and select "Group". Set a grouping interval that suits your purpose. In the screenshot I set a group interval of 10.

advance.png

You can insert a timeline slicer to show data for a specific time frame only (Pivot table Analyze ribbon > Insert Timeline.

Let me know if that is something you can work with.