2019: Week 40

This week is Tableau Conference week. A major conference happened last week and they need some Data help! Sudzilla, the annual conference for all things soap, was attended by our very own Chin & Beard Suds Co. A survey was sent to all participants and the results are in but the structure of the data isn't ideal for the analysis we want to do.

Summarising survey responses to understand the overall event is challenge. One metric that often gets used is Net Promoter Score (NPS). This takes the questions "On a Scale of 0-10, would you recommend [Enter Event / Product] to friends and family?" and converts it in to a percentage score. The reason why NPS is better than traditional satisfaction scores is the customers who have poor experiences will do damage to your brand (detractors) rather than just looking at those who are promoting your brand (promoters). 
Through research, Promoters were found to be those who gave scores of 9 or 10. Detractors were a large range of 0-6.…

How to...handle Prep errors

***Disclaimer - Prep is rapidly developing so we will try to edit this post to keep up but some 'knowledge' might age but we'll do our best***

Errors in software suck. Who hasn't spent time crying into their keyboard or banging their head against their screen asking the computer gods to find a solution to their problems? Agreed - no one! At Preppin' Data, we are fortunate to have taught a lot of people self-service data prep and Tableau Prep so have a good view on common mistakes people make with the tool. This post will address some of those common issues and pose some potential solutions.

Here's a list of the errors we are going to cover:

ParametersBlank profile and data panesWhere's my input file? Why is my input showing as rows rather than columns of data? Parameters "...But parameters aren't a thing in Prep" I hear you all cry - correct but you will see this error message a lot:
For those on a small screen the error message reads "Fun…

2019: Week 38 Solution

You can view our full solution workflow below and download it here!

There are 3 aggregations required for this challenge. We’ll tackle the easier two first.
Aggregate to get the total patient visits and first visit dates After importing the data simply create a new aggregation step with the following settings:
GROUP on [Patient ID]COUNT DISTINCT[VisitID] fields and rename to [Total Patient Visits].You could also SUM the [Number of Rows] field for this.Take the MIN of [Date of Service] and rename to [First Visit Date]. This gives us the [Total Patient Visits] and [First Visit Date]s for each patient. We can now set this to one side whilst we go back and perform the last aggregation required. Use a self-join & aggregation to number the visits. We now need to number each patient’s visits so that their first visit is number 1, second visit is number 2, and so on. To achieve this we can use the running sum trick.

First, duplicate the data in a new clean step. Then join the data to itsel…

2019: Week 39

The Head Honchos of Chin & Beard Suds Co want to understand how their website is performing. Are the web pages being tested on the right devices and Operating Systems? Are we considering language and cultural references?

To conduct this analysis, our website stats are held in a frustrating way but this analysis won't be done just once so we want to set up the flows so they are available in a reusable way to conduct this analysis whenever needed.  Requirements Input the Excel workbookCreate a single table for each of Origin, Operating System and BrowserClean up values and percentagesConvert any < 1% values to 0.5If percent of total fields don't exist in any files, create these and make them an integerCreate a consistent output for all three files Outputs
3 Files with the following 6 columns: Type (either Origin, Browser or Operating System)Change in % page viewsThis Month Pageviews ValueThis Month Pageviews %All Time Views ValueAll Time Views % The number of rows (excludin…

2019: Week 37 Solution

You can view our full solution workflow below and download it here!
Use a wildcard union to import all the data. When importing the six different files, we could individually add each file to the canvas and then use five union steps to combine them. A much more efficient and dynamic solution is to add one of the files to the canvas and then use a Wildcard Union.

Drag one file onto the canvas.Select the Wildcard Union option.
If the folder with the files in has no other files in it then click Apply.If the folder with the files in has other files in it, or may have other files in it in the future, then set a Matching Pattern to ensure only the appropriate files are imported. A matching pattern for this import could be: MOCK_DATA-*.csv. Click Apply after setting the matching pattern.
After clicking apply, a new field called File Paths should appear in the table of fields. Make sure this is ticked as we need it for the next stage.
Creating the date field. To create the date field, we need t…

2019: Week 38

This week's challenge comes from a Data School consultant - Collin Smith. He used Tableau Prep Builder as described below before he joined The Data School and has been enjoying the Preppin' Data challenges so wanted to contribute his own challenge. I hope you enjoy it and let him know how you get on! _____________________________________
Last spring, I worked with Health in Harmony, a planetary health organization in Borneo, to help get them set up using Tableau Prep and Tableau Desktop. One of my big jobs was to re-create a medical data workflow that was originally in SPSS with Tableau Prep. The challenge this week comes from one of the steps of that workflow that involves heavy use of the aggregation tool and some clever join logic. I’m heavily indebted to the Tableau Service Corps and Community Forums for guiding me through this the first time.
Requirements Use the join and aggregation tools to find metrics on each patient based…

2019: Week 36 Solution

You can view our full solution workflow below and download it here!
1.   Convert the height for the Spurs.Firstly, we’re going to create the height conversion step. There’s a number of different ways to convert the feet & inches into meters here. An easy method is as follows:

Remove all punctuation from [HT] using the relevant Clean option.Use an automatic split to split [HT] into [Feet] (split 1) & [Inches] (split 2).Calculate the [Height (in)] (height in inches) using: [Feet] * 12 + [Inches].Convert this to [Height (m)] (height in meters) using: [Height (in)] * 2.54 / 100.
We divide by 100 as without it we actually have the height in centimetres.Round this to 2dp using the ROUND() function. After this, make sure to remove the original [HT] fields as well as all the unnecessary fields created along the way.

2.   Convert the weight for the Spurs. We’re now going to create the weight conversion step. There’s less work to be done in order to convert the weight.

Remove all letters a…