Preppin' Data

Challenge by: Jenny Martin We've reached the end of another year and so, before we look forward to the next year, let's take a look back! This challenge is our 255th! Thank you to all who support Preppin' Data!  Running this community project takes a lot of hard work, creativity and time behind the scenes, but something we've been asked for for many years is a way to understand the difficulty and techniques used by past challenges. At last, we bring you the data behind that! I've been chipping away at this in the background for many, many weeks and, unsurprisingly, there are definitely a few inconsistencies that we'll need to clean up! Never let your users have free text input, right?  This will not only help everyone participating in Preppin' Data find the challenges they're most interested in, but it will also help us identify gaps and create challenges that continue to use different skillsets. At some point, it will also give us the opportunity to bui...

Challenge by: Jenny Martin We're getting festive for the penultimate Preppin' of the year! And more than that, we're collabing with Workout Wednesday! So once you complete this challenge head on over to Workout Wednesday to build a fun dashboard with the data. Santa has noticed his elves, while enthusiastic, are not always the most predictable when it comes to toy making. He sets quotas for toys at the start of the year but the way the elves track their toy building doesn't make it easy for Santa to tell if they'll have enough toys for Christmas deliveries. So he's looking to get prepared for next year and get the Elves' Toy Building Tracker connected to a Tableau Dashboard so they can see where to focus their enthusiasm.  The only problem is that their tracker, whilst festively themed, will need a little reshaping before it's optimal to work with in Tableau Desktop. It may or may not be based on an Excel file I encountered in the real world this year! ...

Solution by Tom Prowse and you can download the workflow here . This week we continue from the previous challenge and take our analysis a little bit further. Step 1 - Create Parameter First we want to introduce a parameter so that the user can control the input value that they want. For this we want it to be a list of values from 50 to 500 in increments of 50 Step 2 - Max Monthly Deposit We can now start using the parameter within our previous workflow. First we want to identify how the value in the parameter compares to the monthly max deposit from the supplier. This can be input into the existing workflow by adding some additional steps between the existing steps:  For this we can create a new calculated field that includes the parameter value, then we can use a MIN function to identify the minimum value between the parameter and max monthly deposit. Monthly Deposit MIN([Customer Planned Deposit],[Max Monthly Deposit]) Then we can calculate what is 'leftover':  Leftover savi...

Challenge by: Jenny Martin Data Source Bank (DSB) were so thrilled with the workflow produced for last week's challenge . As often happens with great analysis, further questions were raised.  Which would be the best account for a customer given the amount they're able to deposit each month?  Of course, it made sense for the previous challenge that the accounts that allowed higher monthly deposits ranked highly, but what if a customer only has a fraction of that monthly deposit, which account would best suit them then?  Input We'll be tweaking the workflow that was built last week, which can be found here - or of course you can use your own workflow from last week! Requirements Introduce a field which allows a used to control their Planned Monthly Deposit If this value is greater than the Max Monthly Deposit allowed by the Provider, then calculate how much will be left over and receive no interest Although you'll want to make sure these leftovers are added on after the ...

Solution by Tom Prowse and you can download the workbook here . Step 1 - Update Fields First we want to make the Max Monthly Amount field numeric so we can update it using this calculation:  Max Monthly Amount   REPLACE([Max Monthly Deposit],'£','') We can then change the data type to a whole number.  Next, we want to update the Provider field so that the additional conditions are easily identifiable. Here we can use an IF statement to update the provider field:  Provider IF [Has Additional Conditions]='Y' THEN [Provider]+" (Conditions Apply)" ELSE [Provider] END At this stage the table should look like this:  Step 2 - A row for each month Next we want to create a row for each month that we will be using the account. For this we need to create two date fields and then use these to pad out the additional rows. 1st Month MAKEDATE(2024,1,1) 12th Month MAKEDATE(2024,12,1) Then we can use the New Rows step to pad out the additional months:  Finally, we can ...

Challenge by: Jenny Martin Data Source Bank (DSB) are looking to introduce a Regular Savings Account for their customers. There are 2 factors they need to decide: The interest rate The maximum amount a customer is able to pay into the account each month DSB are looking to compare other 12 month products currently in the market, to help decide these factors. They want to understand which account offers the customer the biggest opportunity to increase their savings. They also want to educate their customers by showing how their savings would increase each month.  Input 1 file containing the details of 12 month regular savings accounts currently available in the UK Market as taken from Money Saving Expert Requirements  Note: We assume the interest will be spread evenly across the 12 months for this challenge Input the data Update the Max Monthly Amount so it is a numeric field Update the Provider field so it is easy for customers to see in 1 field which accounts have additional c...

Solution by Tom Prowse and you can download the workflow here . Step 1 - Selected Reporting Year First step this week is to input the data and then create a parameter that will allow the user to select a reporting year.  The parameter should be a whole number with the options of 2020, 2021, 2022, 2023, & 2024 to choose from.  We can then use this in our workflow as a calculated field containing just this parameter:  From here we can utilise this field to create a start and end date for the selected period.  For the start date we use the following calculated fields:  Start DATE(DATETRUNC('week',MAKEDATE([Year]-1,2,1),'Monday')) This will find the Monday before the 1st Feb. End MAKEDATE([Year],12,31) The end date is always the end of the selected year. Step 2 - Reporting Year Next we want to fill out the dates for the reporting year by first calculating the start of the reporting year: Start of Reporting Year DATE(DATETRUNC('week',MAKEDATE([Year],2,1),'Mon...