2021: Week 37 - Solution


Solution by Tom Prowse and you can download the solution here. 

This week we had a look at the new generate rows feature within Tableau Prep. This allows us to fill a range within two numbers or dates so that we can generate rows that weren't already existing within the data set.

We revisited the 3rd ever Preppin' Data challenge for this one to see how much easier the new feature makes the solution instead of having to use an additional table to scaffold the data.

Step 1 - Create End Date

Our data table has a start date and a contract length, therefore using this we can create a date for when the contract will finish.

End Date
DATEADD('month',[Contract Length (months)]-1,[Start Date])

We don't want to include the current month so we need to subtract 1 from the Contract Length field and then we can use this date to 'fill in' the gaps between the start and end months.

Our table now looks like this: 


Step 2 - Fill in Rows

This next step is where the new feature magic happens! There is a new option in the list of steps that we can create 'New Rows'.


From here we have a few different options on how to configure the tool. We can choose between values from a single field, or values from a range. As we want to go between the start and end dates, we will choose the range of values. 

Next, we set up the conditions so that the start date is less than or equal to the end date, then we want to specify the time period between (1 Month), and finally what we want to copy for the new rows (the value from above). 

With this setup we will create a structure that looks like this (where the months between the start and end date have been created):


As a result our data table should now look like this:


Step 3 - Running Total

The final step this week is to create a cumulative total for each person over their contract period length. Traditionally we would do this by utilising a self-join and then follow the steps documented here. However, @hiroakimo_tw pointed out on Twitter that you can use window calculations within Prep to solve this problem. To create a running total you can use the following calculation: 

Cumulative Monthly Cost 
{ PARTITION [Name] : { ORDERBY [Payment Date] ASC : WINDOW_SUM([Monthly Cost])}}

Note, this technique hasn't been officially released by Tableau so there might be some bugs, but for this use case it works!

After calculating the running total we are ready to output our data set which should look like this: 


You can download the full output here. 

After you finish the challenge make sure to fill in the participation tracker, then share your solution on Twitter using #PreppinData and tagging @Datajedininja, @JennyMartinDS14 & @TomProwse1

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! 

Popular posts from this blog

2022: Week 14 - House of Games Winners

Image
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 ...
Read more

How to...Handle Free Text

Image
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 ...
Read more

2024: Week 41 - Forbes Highest Paid Athletes

Image
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 ...
Read more