2023: Week 13 - Solution
Solution by Tom Prowse and you can download the workflow here.
This is our final week of the advanced challenges and themed months to start off the year. Hopefully, you have been able to develop your skills each week and now have some top techniques to use in your own analysis.
The challenge this week focusses on moving averages within Tableau Prep. This isn't a native feature so we have to get a little creative to solve this type of problem. Let's look at how it can be done!
Step 1 - Input Data
First, we want to input the data from all of the different CSV files within the folder. This contains all of the information for each month so we want to utilise the wildcard union to bring all of these together into a single input.
After using the wildcard union, we can then clean the table by removing first_name, last_name, Ticker, Market, Stock Name, and Market Cap fields.
Then from the File Paths field (this is created by the wildcard union) we can remove all letter and punctuation so we only have numbers remaining.
Then finally we can replace the blank field with a 1, so we then have each of our month numbers.
At this stage our table looks like this:
Step 2 - Trade Order
To create the trade order we can use the Rank calculation where we group by Sector, then rank the File Path and ID fields:
We can then remove any other fields so we only have Trade Order, Sector, and Purchase Price in our table.
Then we can create a parameter so that the user can control how many trades should be incorporated within the moving average.
For this we want a whole number where all allowable values can be included:
Then finally we need to create a calculated field so we can use the parameter. In this case we want to find the previous N trades so we want to subtract the parameter from the Trade Order field.
Min Trade Order
[Trade Order] - [N Previous Trades]
Our table should now look like this:
Step 3 - Rolling Average
Next we want to start preparing the data set so that we can calculate the rolling averages. First we need to create a data set that records the previous 2 (/N) trades as well as the trade order. To do this we want to use a self-join, so need to create a new clean step and then join back to the original.
For the join, we want to set up an inner join where Sector = Sector, Trade Order <= Trade Order, and Trade Order > Min Trade Order:
This will massively increase our number of rows, then allows us to realign the previous trades with the current trade.
At this stage we can rename the Trade Order-1 field to Trade Order (True) and remove the Sector-1, Purchase Price-1, Min Trade Order, and Min Trade Order -1 fields so the table looks like this:
You'll notice that we now have 3 (/N) rows for each trade. Using this duplication we can now aggregate to calculate the rolling averages.
First, we want to calculate the average price by grouping by Trade Order (True) and Sector, then Avg Purchase Price:
This gives us the rolling average for each Trade based on the parameter.
Step 4 - Last 100 Trades
Previous 100 Trades
[Trade Order (true)]-100
Then we can use this list of the previous 100 trades to join back to our original aggregation where Sector = Sector and Previous 100 Trades < Trade Order (True)
After the join the table should look like this:
Finally we want to only show the oldest trade through to the latest trade so need to rank the previous trades based on the trade order for each sector:
We can then rename Trade Order (True)-1 to Trade Order, and Purchase Price to Rolling Avg. Purchase Price. Then remove Trade Order (True), and Previous 100 Trades. Also, we can round the Rolling Avg. Purchase Price to 2 decimal places:
Rolling Avg. Purchase Price
ROUND([Rolling Avg. Purchase Price],2)
Then we should be ready to output our table that looks like this:
