In preparation for my second attempt at the Alteryx Core Certification tomorrow I have been working through Alteryx challenges. On the Alteryx community page, there are useful 'Labels' for the list of weekly challenges, so you can filter by difficulty. This has been very helpful in looking to complete the 'Core' level challenges.
For this challenge, I was given data on dates people arrived and checked out of a hotel. I was challenged with finding the average length of stay per month, not including 1 day stays and cancelations, and the number of reservations in that month.
First, I wanted to filter out all Cancelled bookings, by using a filter tool, and returning true if the Field 'Reservation Status' = Check Out.
Next I wanted to standardise and clean up the date fields, so I had a Check in and a Check Out date for each Reservation.
I first used the text-to-columns tool to split the reservation-status-date field into 3. I then used the select tool to remove unnecessary fields, and lastly, used a formula to combine the month reservation status date and the day reservation status date fields to form a new Check Out date field:
From here, I used a formula tool to create one field for arrival date. I then converted the arrival date and Check Out Date fields into date/Time Format using the 'DateTime' tool.
I joined these steps back together, using the embedded select function to select only the relevant Fields.
Next, to calculate the difference between Arrival and Check Out date, I used a 'datediff' function in the formula tool and set my measure as 'days'. I then filtered my results to ensure I was only seeing reservations who stayed at least 1 day.
Lastly I used the 'Summarize' tool to Group by month, and then calculate the average for number of days stayed, and counting the distinct number of reservations.
My final workflow looked like this:
