2019: Week 40 Solution
Output 1 – NPS Score
First we need to count the amount of respondents who are classified as a Promoter or a Detractor according to the NPS scoring system. This can be done by using the following IF statements to count each category:
Promoter
IF [On a scale of 0-10, how would you rate Sudzilla?] >=9 //If the score is greater than or equal to 9
then 1 //then mark as 1
ELSE 0 //If less than 9 marks as 0
END
Detractor
IF [On a scale of 0-10, how would you rate Sudzilla?] <=6 //If the score is less than or equal to 6
THEN 1 //then mark as 1
ELSE 0 //If less than 9 marks as 0
END
Now we’ve categorised each response, we need to count how many promoters and detractors we have. To do this we can aggregate with the following fields:
As a result of the aggregation we have three fields:
- Detractors
- Promoters
- Number of Rows - This can be renamed to ‘Total Respondents’
The last step is to use the aggregated fields to calculate the NPS score. This is calculated with the following equation:
NPS Score
([Promoter] - [Detractor]) //NPS calculation
/
[Total Respondents])
*
100
I have also used the Round() function to round the answer to 1 decimal place.
Output 2 - Detailed Output
The next task is to clean and create a detailed output of the survey responses.
The first step is to split the ‘Which three words would you use to describe to Sudzilla? (separate with a comma)’ field into three separate columns. We can do this by using Tableau Prep’s split function, and use a custom split to split all values by ‘,’.
To combine the three newly created columns, we used the pivot function and selected the three split columns.
Next step is to tidy up some unwanted fields and columns from the result of the pivot. We want to remove any null values from the ‘three words’ column, and remove the ‘Pivot Names’ field.
To calculate the avg food rating we need to rename the values within the food related fields. First, I converted the responses to a 1-5 rating, by renaming the fields to the following:
1 - Horrendous or Null
2 - Just about edible but I was hungry
3 - Some good, some not so good
4 - Yum!
5 - Give the team a Michelin star!!
Then the next step was to use a calculated field to calculate the average across the three courses:
Avg Food Rating
(int([How would you rate the food at Sudzilla (breakfast)?])
+
int([How would you rate the food at Sudzilla (lunch)?])
+
int([How would you rate the food at Sudzilla (dinner)?])
)
/
3
*Note - You can convert the fields to a number using the data roles before creating the calculation.
We used a similar calculation to find the average rating from the Keynotes:
Avg Keynote Rating
(
[On a scale of 0-10, how would you rate the opening keynote?]
+
[On a scale of 0-10, how would you rate the closing keynote?]
)
/
2
Then the final step was to remove any unwanted fields before creating the final output tool.
