Preppin' Data

Challenge by: Jenny Martin Chin & Beard Suds Co release Easter themed products each year, 12 weeks before Easter Sunday. Since the date of Easter Sunday changes each year, they have trouble comparing sales to previous years, as the weeks don't line up. They're looking to create the following dashboard to get some insight into their sales in this final week leading up to Easter Sunday.  Inputs We have an Excel extract that contains a sheet for each year that C&BS Co have sold Easter themed products. Requirements Input the data Bring all the years together in a single table Work out the Easter Week Number, as per the first line chart, so the years can be compared Ensure that the weeks begin on a Monday Make sure there is a field for which day of the week each Sales Date is in order to create the 2nd chart Abbreviate these weekday names as above i.e. Monday = M Tuesday = Tu  etc. Add in a field for the Weekday Order so the visualisation can be sorted Monday-Sunday i.e. Mo...

Solution by Tom Prowse and you can download the workflow here .  Step 1 - Course Dates First we need to calculate when the course starts and ends then build out a table with each of the months in-between. To do this we need to create the course end date to add on the course length from the start date:  Course End DATE(DATEADD('year',[Course Length (years)]-1,[Course Start])) We can then use the Start and End dates to create a new row for each of the months in-between using the New Rows step.  Then finally we can calculate the total amount per year for each of the loan types by using an aggregation step where we group by Loan Type, Amount per Year, and Year:  At this stage our table will look like this:  Step 2 - Loan Dates Now we want to calculate the dates for when the loan has been received. First we can create a date based on the year of study and the 1st September:  Loan Received MAKEDATE([Year],9,1) Then we want to calculate the number of months since ...

Challenge by: Jenny Martin Prep Air employees had positive feedback about the Staff Income Tax challenge a couple of weeks ago, so they have decided to carry out a similar exercise for staff on the Graduate scheme.  In England (where Prep Air is based), graduates will begin to repay their Student Loans the April after they graduate. This will be the first time many graduates will see the student loan deduction on their pay slip, so Prep Air wants them to know what to expect. The amount they repay is based off their salary rather than the size of the loan.  More info on gov.uk The graduates at Prep Air are all on Plan 2 student loans, so they pay 9% on anything they earn over the threshold of £27,295. In contrast to when repayments begin, interest on the loan starts accumulating from the day the first payment is made. Interest is applied monthly. We're going to simplify the problem a little and assume that the full loan amount is paid to the student once a year (even though in...

Solution by Tom Prowse and you can download the workflow here .  The challenge this weeks focusses on manipulating dates so that we add in an additional month to the year so we have 13 months in total.  Step 1 - Every day of year First off we want to create a row for every day of the year. We are given our start and end date within the input, so first we want to remove any Null values then create our date field so that it includes the year:  Date [Date] + "2024" Then we can change the data type to a date so we have a nicely formatted date field. At this stage we should have two rows with two dates - 01/01/2024 and 31/12/2024.  We can then use these dates to create a row for each day between each of these using a New Rows step.  Now we have every day of the year we can then create a field for the Month number and Day of the Year number.  To do this we will duplicate the Date field, then convert this to Month Number using the in-built functionality However, w...

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...

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...

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 ...

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 ...