This week we continued our fundamental challenges by focussing on a common problem in data preparation. That problem involves including data about something that doesn't happen. Our example is we're trying to identify the days when there were no sales at our stores. Let's see how we solved the challenge.
Step 1 - Days with no sales
First up we want to convert our date field from a datetime to just a date. This can be done by pressing on the calendar and clock icon in the top left of the field in the profile pane, and then changing to a Date option.
We can now go and add some new rows for days when there were no sales. For this use the 'New Rows' step to help us.
After bringing the New Rows into our workflow, we need to set this up so that we are creating a new row from our minimum to maximum sale date:
This will allow us to fill in the missing days and will create a new row for each of them. Our table should look something like this, where if there are no sales on that day, the row should have 0s and nulls:
We are only interested in the days where there were no sales, so we can filter the Sale Value and keep only 0:
Step 2 - Days of the Week
Now we have identified all of the days where there are no sales, we can now do some further analysis based on which days of the week this occurs most regularly.
First we can remove any fields that we no longer require. This includes OrderID, Sale Value, Product Name, Store Name, Region, and Scent Name. We should be left with just the Sale Date field.
Using the Sale Date we can duplicate this by clicking on the three dots and then choosing duplicate. Once duplicated, we can use the Convert Dates feature to convert one of the date fields in days on the week:
Now we should have two fields, Day of the Week and Sale Date
The final steps for the challenge are to total the numbers by each day. For this we can use an aggregate step where we group by Day of the Week and CountD Sale Date
The final task is to rename Sale Date to Number of Days and then we are ready to output our table:
You can also post your solution on the Tableau Forum where we have a Preppin' Data community page. Post your solutions and ask questions if you need any help!
Challenge by: Jenny Martin As I've mentioned before in a previous challenge, I'm a big fan of a quiz show called Richard Osman's House of Games. However, I've often found the way that they decide the overall winner of the week a little troubling. Each day the player who has scored the most, will receive 4 points, 2nd place will receive 3 points, 3rd place will receive 2 points and last place will receive one point. These points will be added up across the week to determine the overall winner, but with a twist! Each Friday double points are awarded so 1st place receives 8 points and so on. This leads me to wondering: Would there be a different winner if there was no double points Friday? What about if participants weren't ranked at the end of each day and they had a running total score across the week instead, would that lead to a different winner? What about doubling the scores on the Friday, instead of the points awarded? Input Luckily I didn't have to collect ...
Free isn't always a good thing. In data, Free text is the example to state when proving that statements correct. However, lots of benefit can be gained from understanding data that has been entered in Free Text fields. What do we mean by Free Text? Free Text is the string based data that comes from allowing people to type answers in to systems and forms. The resulting data is normally stored within one column, with one answer per cell. As Free Text means the answer could be anything, this is what you get - absolutely anything. From expletives to slang, the words you will find in the data may be a challenge to interpret but the text is the closest way to collect the voice of your customer / employee. The Free Text field is likely to contain long, rambling sentences that can simply be analysed. If you count these fields, you are likely to have one of each entry each. Therefore, simply counting the entries will not provide anything meaningful to your analysis. The value is in ...
Challenge by: Robbin Vernooij Recently, one of the Data School Coaches, Robbin, set the following challenge. It seemed perfect for a Preppin' Data, so over to Robbin: We'd like to get historical data on the highest paid athletes so we can do temporal analysis. Lucky us, it turns out Wikipedia has been tracking the Forbes list of the world's highest-paid athletes. Unlucky us, it is in an HTML table format with human readable symbols and table by table basis. Now it's time for you to clean it up into one single dataset, so that it's ready for analysis. Inputs The data for this challenge comes from this Wikipedia page . There is a table for each year that looks like this (2024 example): As well as a source table: Requirements Input the data Bring all the year tables together into a single table Merge any mismatched fields (there should not be any Null values) Create a numeric Year field Clean up the fields with the monetary amounts One way of doing this could ...
