We have a MySQL database of satisfaction survey results for customers who have used a service. One of the questions asks for a score on a scale from 1-5 for Overall Satisfaction with the service.
Those who score 1 or 2 get labelled as Dissatisfied. Those who score 4 or 5 get labelled as Satisfied.
We are trying to calculate a Z Score (number of standard deviations away from the mean) for the percentage of people who answered Satisfied for each service, but we are coming unstuck when attempting to calculate the standard deviation for this data. The STDEV function does not like the argument we are trying to give it.
RawScore calculation: the percentage of Satisfieds in the not null answers
count(if [Overall Satisfaction] = "Satisfied" then 1 else null end)
MeanOfPopulation calculation: the percentage Satisfieds in the not null answers for the entire data set
total(count(if [Overall Satisfaction] = "Satisfied" then 1 else null end))
total(count(if not isnull([Overall Satisfaction]) then 1 else null end))
Every argument we have tried to supply to STDEV is not accepted because it is an aggregate. We cannot supply just the field because it is not quantified data, and we cannot supply the percentage of Satisfied responses because it is an aggregate. The Tableau documentation method for creating Z Scores (http://kb.tableausoftware.com/articles/knowledgebase/z-scores) does not work for the same reason.
Attached is a packaged workbook containing some sample data. In reality there are many more services.
Please don't hesitate to ask if you need more information.