2023: Week 6 - Solution

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

We continue this month with the 2nd intermediate month challenge. This helps to build on our knowledge from previous weeks and cover more data prep techniques. For the challenge this week, we are looking at Survey data which can be notoriously hard to work with when preparing for visualisation.

Step 1 - Reshape Data

After inputting our data we want to change the shape from wide to long. We can do this by pivoting all of the fields that contain a score into a single field, however we don't want a single value field but for it to bit split out based on Online or Mobile.

We can create this 2nd value field by using a handy technique in the Tableau Prep pivot step. Normally, we only add in a single pivot values, however you can press on the little + icon to add a 2nd set of values. Within each of the values we can use a wildcard pivot on 'Mob' and 'Onl' and this will create the split of the scores:

We can then tidy the field values by removing the 'Mobile App' part by using a split (automatic split should work here), then renaming the fields to something more understandable. Our table should now look like this: 

Step 2 - Average Ratings

In the next step we want to calculate the average ratings for each platform. Before doing this we need to filter and remove any rows that contain 'Overall Rating' by selecting in the profile pane and 'excluding'. 

We can then use an aggregation step to group by Customer ID and then Avg Online Interface & Mobile App: 

Then we can calculate the difference between the Avg for both platforms: 

Difference

[Online Interface]-[Mobile App]

Step 3- Preference Categories

Now we know the difference between the platforms, we can categorise these results to help create custom categories for the customers.

We can use the following calculation to create these categories: 

Preference

IF 

  [Difference]<=-2

  THEN 'Mobile App Superfan'

ELSEIF 

  [Difference]<=-1

  THEN 'Mobile App Fan'

ELSEIF 

  [Difference]>=2

  THEN 'Online Interface Superfan'

ELSEIF 

  [Difference]>=1

  THEN 'Online Interface Fan'

ELSE 'Neutral'

END

This calculated field will help to then create a percent of total for each of these customer categories. 

We will need to first aggregate based on the new preference field and countd of customer ID: 

We can then calculate the total number of customers across all categories by using a Fixed LOD: 

Total Customers

Then the final task is to calculate the % of total: 

% of Total

ROUND(

[Number of customers]*100

/

[Total customers]

,1)

After tidying the remaining fields, our output 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!