Preppin' Data

Challenge by: Jenny Martin This week we're tackling some questions commonly asked by clients: How do I calculate working days in Tableau Prep? or Is there a networkdays function in Tableau Prep? Commonly, clients are looking to calculate the number of working days between the open and closing date of support tickets, to see if they're complying with their SLAs.  However, I thought we'd choose something a bit more fun for the challenge this week! Instead, let's work out how many days you've worked in a specific time period. This could be since you got your first "proper" job, since you started working for your current employer, days you've worked this year - anything you like! Inputs The inputs will need to be a little bespoke for this challenge. You will need: An input containing the date you'd like to start counting from An input containing information about the number of days holiday you took each year A bank holiday input for your country of r...

  The solution this week is by Tom Prowse and you can download our workflow from here .  This week we took a look a school scheduling and if we had enough teachers and if the school is asking too much from them! Step 1 - Students per Year per Subject  The first step this week is to calculate how many students we have in each year and each subject. Using the Students table, we need to split the subject field so that we have a column for each subject. We do this by using a custom split - All values using the separator '/': After this we have a column for each Subject:  We now want to bring these all back into one column by using a pivot tool. We use a Columns to Rows pivot, using the wildcard pivot on 'Subject':  After the pivot we can remove the Pivot Names, rename Subject Split to Subject, and Exclude any empty fields from the Subject field. Our table should now look like this:  The final step in this part of the challenge is to use an aggregate tool to fi...

 Challenge by: Carl Allchin It's back to school for the UK this week so we thought we'd help out at our local school by trying to understand how busy the teachers are going to be. We've decided to focus on the Science department as we've heard murmurs of discontent about how hard some teachers are working. But is the school asking too much of the teachers? Do we have enough teachers? Input One file that you can get here but four tables: 1. Teachers 2. Students 3. Rooms 4. Hours Requirements Input the data Determine how many students are in each year for each subject Work out how many hours of teaching is required for the students based on the age of the students Work out the room capacity for each subject By assessing the number of days someone works, how many hours of teaching are potentially available within a week: Each day has 6 hours of potential teaching 9-12 and 1-4 Each teacher can only teach certain subjects and certain ages Teachers who cover multiple subject...

Solution by Tom Prowse  and you can download our solution here . This week we had the option to take a look at the scripting tool for the first time! In the challenge we looked at creating a Row ID in the script tool by using either TabPy or RServe. Within this solution we will cover how to solve using RServe, but we would recommend looking at this blog post for how to create a Row ID using TabPy. Our task was to format the Sales and Budget table being used by Chin & Beard Suds Co. We want to do this in the most dynamic way possible so that we can automate the process going forwards.  We will cover both the solution for the input including the Row-ID and also using RServe to create the Row-ID. Step 1 - Data Interpreter When we first bring the data into Tableau Prep, it's a bit of a mess as it is bringing it through in the same format as in the spreadsheet. Therefore, our first task is to use the Data Interpreter to help clean this up, so that the data now looks like ...

Challenge by: Jenny Martin This week we're looking at a slightly odd way that Chin & Beard Suds Co have been structuring their store sales and target data:   As you can see, for each Store, there are 3 rows, the first being the Sales values, the second row containing the Target values for each month and the third row containing the difference between these values. It's your job to transform this monstrosity  unique table into a more conventional output that we could use in Tableau Desktop.  Inputs You have 2 options this week: Start the challenge with a Row ID already present (as pictured above) Use this as an opportunity to play with the Script step and create your own Row ID! Our solution will cover the RServe option, but you're welcome to use TabPy instead. For help getting set up with RServe, check out this blog For TabPy, check out this blog by our colleague Brian Scally   Requirements Input the data Make sure Store names have filled down correctly and remo...