Posts

2024: Week 11 - 13 months in a year

Image
Challenge by: Jenny Martin Are you familiar with the argument that there should be 13 months in the year instead of 12 ? 13 months, exactly 4 weeks long, would account for 364 days of the year and then we'd just have an extra 1 to deal with (or 2 on a leap year!)  Naturally, my mind goes to building a workflow that let's me investigate which dates would change months. Naturally, Carl's mind starts wondering what the new month would be called, so do let us know your suggestions! Input Just a small input this week: Requirements Input the data Create a row for each day of the year I've chosen to use 2024 for the challenge so results will be different if you select a non-leap year Calculate the new months of the year such that the first 28 days of the month are month 1, the next 28 days are month 2, etc This will give you 14 months, with the 14th month containing just 2 days Create a new date with the format: New day of the month / New month / 2024 e.g. 20/11/2024 becomes 1

2024: Week 10 - Solution

Image
Solution by Tom Prowse and you can download the workflow here .  The challenge this week is focussing on Tableau's new release of Tableau Pulse. Tableau Pulse is great for empowering employees to make better, faster decisions by tracking a metric's current value and comparing how it's changed. Unfortunately, this doesn't happen by magic and the underlying data needs to be sorted to display these metrics effectively... that's where Tableau Prep comes in! Step 1 - Dates Tableau Pulse works best when we have our data at a daily or weekly level so we can first ensure that our transaction data is structured in the most effective way.  First we want to only look at the last 2 years of data and in this case we want years 2023 & 2024. We need to ensure that our Transaction Date is a date field (not a string) then we can filter to keep only the years 2023 & 2024. There are multiple ways of filtering for this, but in a real life scenario you'll want this to be upd

2024: Week 10 - Preppin' for Pulse

Image
Challenge by: Jenny Martin Tableau Product Manager, Libby Knell has challenged us to create a Preppin' Data challenge that gets data in shape for working with the newly released Tableau Pulse . Pulse empowers every employee to make better, faster decisions by tracking a metric’s current value, compared to the past — so what does this mean for the shape of our data? Currently, Pulse works best with: Data that is up to date and recent - the last 1 or 2 years and changes regularly - daily or weekly preferable Data that is complete, without gaps Data that is as granular as possible The names of fields are human readable - let’s not use acronyms that lack context! String values in the data are consistently named Chin & Beard Suds Co are excited to get started using Tableau Pulse so their employees can make smarter decisions for their flagship store. Their store manager is always on the go and works off their phone daily. With the insights Pulse provides, they’ll be better able to ke

2024: Week 9 - Solution

Image
Solution by Tom Prowse and you can download the workflow here .  Step 1 - Cancelled Flights The first task this week is to remove any flights that have been cancelled. To do this we want to input the Customer Action table and from here we need to identify whether the customer has cancelled or not.  We can't just use a normal filter here, because we want to remove all rows related to the customer and flight if they have cancelled. Therefore, we can create a new clean step, then filter to keep only the 'Cancelled' values from the Action field and then keep only the Flight Number & Customer ID fields.  This provides us with a list of all the customer IDs and flight numbers where the customer has cancelled. We can then use this list to join back to our original table using an outer left (or right) join to remove these rows from the original table:  After the join we can remove any unneeded fields and the table should look like this:  Step 2 - Most Recent Action Next we want

2024: Week 9 - Prep Air Capacity

Image
Challenge by: Jenny Martin Prep Air would like to do some analysis on how their flights are filling up over time. They've given a small sample of flights that will be taking off next month, and the actions that customers who have booked those flights have been taking.  Inputs A customer actions table in which a new row appears each time a customer takes an action relating to their flight booking  A flight details table detailing how many seats are available for each class on the flight  Requirements Input the data If the customer has cancelled their flight, make sure all rows are filtered out for that flight  For each customer on a flight, filter the dataset to their most recent action Based on the Date field, create a field which shows how many seats in total have been booked as of that date for each flight and class Hint: Running Sum could be useful here! Bring in information about the Flight Details Calculate the Capacity %: of the available seats on the flight for each class, w

2024: Week 8 - Solution

Image
Solution by Tom Prowse and you can download the workflow here . Step 1 - Prep Air Loyalty Scheme The first task of this week's challenge is to filter to keep the customers who are part of the loyalty scheme. One of the conditions is that you have to have flown since the 21st Feb 2023 so we need to filter the Customers table to show only customers with a flight after this date:  [Last Date Flown]>=date('2023-02-21') Next we want to categorise the customers into different tiers based on the number of flights they have taken. For this we want to be able to control the bucket size by using a parameter so that the user can select either of the options.  The parameter needs to be a whole number which has a list of values where the user can select 5 or 10:  We can then use this parameter to create the tiers that each customer falls into:  Tier FLOOR([Number of Flights]/[Parameters.Tier Bin Size]) Then finally we can calculate how many flights a customer takes a year, on average

2024: Week 8 - Prep Air Loyalty

Image
Challenge by: Jenny Martin For this week's challenge, Prep Air have asked for some What If? Analysis. They're considering 2 different systems for rewarding customer loyalty and want to understand how that might impact cost and how many customers might benefit from the program. Inputs Prep Air Customer Details - the team were able to add additional information to Week 5's input  Prep Air Loyalty schemes  Prep Air Loyalty Costings  Requirements Input the data To be part of either Prep Air Loyalty Scheme, Customers must have flown in the last year (on or after 21st February 2023) Create a parameter so that the number of flights a customer has taken is either bucketed into groups of 5 or groups to 10 e.g. if the parameter selected is 5, the groupings will be 1-4, 5-9 etc if the parameter selected is 10, the groupings will be 1-9, 10-19 etc Create a field to categorize customers based on the selected parameter, called Tier Estimate the average number of flights a customer takes