Preppin' Data

Challenge by: Holly Jones This is the final challenge that DS43 prepared for us earlier this year - over to Holly:

Solution by Tom Prowse and you can download the workflow here . Step 1 - Sold & Returned First we want to tidy the dates a item was sold or returned so that the return date isn't before a sale date. We can identify any issues by creating an issue flag:  Issue? [Date Sold]>=[Date Returned] Then using this to recalculate the Order or Return date:  Order Date IF [Issue?] THEN [Date Returned] ELSE [Date Sold] END Return Date   IF [Issue?] THEN [Date Sold] ELSE [Date Returned] END We can then remove the Issue?, Date Sold, & Date Returned fields. Finally, we can calculate how many days it took to return an item by using a DateDiff function:  Days to Return   DATEDIFF('day',[Order Date],[Return Date]) And also calculate what the value of the returned items are:  Return Value   IF [Days to Return]<=60 THEN [Price ] ELSEIF [Days to Return]<=100 THEN round([Price ]*0.5,2) ELSEIF [Days to Return]>100 THEN 0 END At this stage our table looks like this:  Step 2 - Cal

 Challenge by:  Jessica Kirk MiniBytes were really happy with the analysis last week on worst performing products. This week they'd like us to look into the returns data so that they can better prepare for how much needs to be set aside for each month for returned items.  They have warned that there has been an issue with the returns systems that means some of the return and sale dates for particular items are in the wrong order (i.e. the sales date has been input as the return date). Input We've been provided with a small subset of data to build the logic from again: Requirements Input the data For all cases where the Date Sold is after the Date Returned, swap the values in these fields Calculate the number of days between the Date Sold and Date Returned For returns within 60 days, customers get a full refund. For returns between 60 and 100 days, customers will be refunded half (rounded to the nearest penny). Returns after 100 days are not refunded MiniBytes want one output to

Solution by Tom Prowse and you can download the workflow here . Step 1 - Sales The first part of this challenge is to calculate the sales value for each product, the total sales per category, and then calculate the % of total for each category. Sales   ROUND([Price]*[Quantity],2) Then we can use this field to calculate the sales per category using a fixed LOD:  Category Sales Now we have the category sales we can then calculate the % of total for each category:  % of Total ROUND([Sales]/[Category Sales],2) The table should now look like this:  Step 2 - Bottom 15%  Now we can filter for the bottom 15% of sales in each category. Before we filter we need to calculate a running total on the % of total:  Running % of Total Then we can use this to filter and keep only the values where  [Running % of Total]<=0.15 The last step is to remove any unrequired fields and our output should look like this: After you finish the challenge make sure to fill in the  participation tracker , then share

Challenge by: Jessica Kirk It's week 43 of the year, so it seems only right to return to the challenges that DS43 created for us earlier in the year:

Solution by Tom Prowse and you can download the workflow here . Step 1 - Year, Week, & Theme After inputting the data source we can start to tidy up the year and weeks for each of the show. To calculate each year we can use the requirements as a guide to when the show started:  Year IF [Series]<=2 THEN 2004  ELSE 2002+[Series] END Next we can extract the week information to include the number and theme. For this we want to split out the information by using an automatic split which will remove the word 'week.  Then we can use that split field to extract the week number and theme using a split on the ':' - an automatic split works here as well.  Finally we want to tidy up the weeks where there is semi-final or final in the brackets. This time we need to use a custom split on the ' ' which will separate the numbers and text and then remove the punctuation from the text field. After we have parsed the final & semi final text we can merge this field with the

Challenge by: Jenny Martin Autumn is always the time of year that Strictly Come Dancing returns to our TVs in the UK. I always find it interesting which songs are chosen for the couples to dance to - particularly when there are repeats. With 22 seasons, this repetition is not surprising, so I set about gathering a dataset that would allow me to see what the most common song choices are.  Inputs I used this as an opportunity to learn to webscrape Wikipedia  using Python (with a lot of help from ChatGPT!), so the resulting table is a combination of each dance from each series: Requirements Input the data One thing the data is missing is a year field for when the Series took place Series 1 and 2 were both in 2004 All following series happen annually Series 3 in 2005 etc. The webscraping isn't quite perfect and the table headers are repeated throughout the dataset, make sure these are removed Split the Week field into a numeric value and put extra details in the theme week  Split this