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:



You can view the 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

2024: Week 1 - Prep Air's Flow Card

2023: Week 1 The Data Source Bank

How to...Handle Free Text