2024: Week 40 - Solution
Solution by Tom Prowse and you can download the workflow here.
Step 1 - Split Users
First up we want to split the users field so that we have a separate row for each user. Currently, they are all in a single row and are separated by a ','. Therefore, we can use the custom split functionality to break these into separate fields:
From here we can remove the original field and then pivot the split fields using a Columns to Rows and the word 'users' as the wildcard option:
At this stage we can rename the Pivot Values to User and then start to create some IDs from the user data.
User ID
LEFT([Users],7)
Private or Dealer
RIGHT([Users],1)
Then we want to keep only the 'D' for Dealers.
Dealership ID
MID([Users],8,3)
At this stage our Users table should now look like this:
Step 2 - Combine Ads Data
Once we have input the Ads data, we can then remove any null values from the sale_date field and make sure it's a Date field type.
We can then join this to our users data using an inner join on the user id field:
Now the tables are together we can start to calculate some more information about the adverts. First we want to calculate when the advert was 1st published by using a Fixed LOD:
Advert 1st Published
Then we can use this field to filter the view so that it only contains the advert that was first posted. The calculation within the filter is [Advert 1st published]=[publish_ts].
Next we can calculate the number of days that it took to sell the vehicle based on when the advert was first posted:
Days to Sell
DATEDIFF('day',[Advert 1st published],[sale_date])
From here we want to aggregate to calculate, on average, how long it takes each Dealership to sell a vehicle. Within the aggregation we want to group by Dealership ID and then find the Avg Days to Sell:
We can then round the Avg Days to Sell up to the nearest whole number using the Ceiling function:
Dealership Avg Days to Sell
CEILING([Days to Sell])
We are then ready to output our data which should look like this:
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!
