Posts

2020: Week 42 - Solution

Image
Solution by Tom Prowse and you can download the full solution here.  This week's challenge was all around calculating the Year/Month/Week to Date calculations which are used throughout various different companies to give an indication about how they are performing. Instead of just basing these calculations on a single date, we wanted to take this a step further and compare the Week and Day number so that we are comparing the same periods throughout the year.This may not be the most simple way to solve it, but I wanted to try and make it as future proofed as possible! For some alternative solutions take a look at the Preppin Data Tableau Forums to see some other community members workflows.Step 1 - Create Daily Targets TableOur targets table is currently only a weekly breakdown so we want to use a daily scaffold to help build this out so we have a target for each day. 
The first step is to bring in the Transactions table, which we will use for the daily scaffold. Within this table we…

2020: Week 42

Image
Challenge by Tom ProwseThis week we are back in the (home) office after a week at Tableau Conference-ish and want to know how we are performing so far this year. As for most businesses, it's been a tough year so we want to find some comparisons between our sales this year, last year and our targets. We have two inputs: 1, TransactionsThis is a list of our daily sales for each product. It contains the Price, Quantity and Income. 
2, TargetsThis is a list of targets that have been provided by the finance team. It is a weekly breakdown for this year by product.
Requirements Input the DataCreate a daily Targets table. Assume there are 7 days in the week and the daily demand is split evenly throughout the week. Eg, if the weekly target is 700, then 100 per day. Categorise whether a row/transaction happened this year, last year or is a target.Combine the Transactions & Targets tables. Only keep the Year to Date for each period. As the 9th October can fall on a different day each year…

2020: Week 41 - Solution

Image
Solution by Tom Prowse and you can download the workflow here
This week was Tableau Conference-ish so we had a special challenge related to Carl's Tableau Prep session! We looked at the comments, from each of the sessions in the different locations, and wanted to know who was commenting, from where they were commenting, and also how many questions & answers there were.Step 1 - Input Files & Create Local DateThe first step is to input all of the files from each session. As all of the sessions have the same structure, we can use the Wildcard Union to input the three files within the same input tool.

In this case we don't need to use anything within the Matching Pattern, but it would be recommend to use a matching string in case any additional files are added in the future. 
Once we have all the files within the workflow, we can create a date time field to show the local date for each of the sessions. The session took place at 2pm on 7th October so we can use the following…

2020: Week 41

Image
Challenge by: Carl AllchinWelcome to Tableau Conference-ish! Every year we like to create a special conference focused challenge. This year is no different. We can't meet in person this year so instead we've arranged to capture the zoom logs of comments from the prep focused session at the Conference. We want to understand who attended and how many questions and answers were delivered through this format. Can you help us understand that? The names are either fictions or some of our regular Preppers who we really wanted to hang out with at the conference so if you see your name - we miss you! (or couldn't think of anyone else from that location). InputsOne file but a few sheets:
Individual session logs that look like:
There are three sessions: one for APAC, one for EMEA and one for AM (the Americas).
RequirementsInput sheetsMake a date time field to represent local time (it will be useful later). The session was on 7th October 2020 at 2pm for everyone.Make sure you know which …

2020: Week 40 - Solution

Image
Solution by Tom Prowse and you can download the workflow here. 
This week we analysed another impressive viz on Tableau Public and decided to take a look at the behind the scenes data prep that was required! We looked at Matthew Armstrong's - 'What Were William's Words Worth' viz and how to clean up the data from the Every Poet website. Step 1 - Clean HTMLThe data is webscraped from a website, therefore is in the form of HTML, CCS & JavaScript, so our first task this week is to remove any rows of data which contains this. This is slightly tricky as there are lots of lines, but first we want to remove any extra spaces from the Line field using the Clean functionality.
Next we can use this calculation within a filter to help catch most of the unwanted lines:
Line
NOT CONTAINS([Line],'<')
OR
CONTAINS([Line],'>')
OR
CONTAINS([Line],'()')
OR
CONTAINS([Line],'=')
OR 
CONTAINS([Line],'e9')
)
This calculation only returns lines that do not co…