Tuesday, March 5, 2013

Calculating the Median of a Likert (Survey) Scale in Tableau

Today, we will look at how to calculate the "median" of Likert Scale in Tableau.  Median is in quotes because this is not a true median, it is more like a weighted average.  However, I believe it gives you far more information than a true median and, therefore, is worth pursuing.  For those of you who are not familiar with a Likert Scale, it is as follows:

1 - Strongly Disagree
2 - Disagree
3 - Neither Agree nor Disagree
4 - Agree
5 - Strongly Agree

This is the common 5-point version; however, there are adaptations of 6 and 7 points as well.  On the bright side, the algorithm in this post will work for any number of points with no alterations.  For the first time, I had to create my own data set for this post.  The sets are as follows:

Sample Data
This idea is courtesy of a poster on the Tableau Forums, Stuart Ramsbottom. (If he gives me a better way to credit him, I will add it here.)  His original post on the Tableau forums can be found HERE.  Set 1 is actually his set of data, while Set 2 is a set I made up to test the algorithm.

Step 1:
  • The median record is found as a traditional median, i.e. Total Number of Records / 2.
  • Create the following calculated field
Median Record
Median Record Test
Step 2:
  • Find the upper threshold for each Likert Category, i.e. Calculate the Cumulative Number of Records.
  • Create the following calculated field
Cumulative Number of Records

Cumulative Number of Records Test
Step 3:
  • Using these upper thresholds, find the Likert Category corresponding the Median Record, i.e. < Threshold( Likert Category - 1 ) < Median Record <= Threshold( Likert Category ).
To me, this seems to be a difficult concept to communicate.  To put it more simply, you must find the Likert Category that the Median Record falls in.

  • Create the following calculated field
Median Likert Category
Median Likert Category Test
Step 4:
  • Find how "far" into the Likert Category the Median Record is, i.e. (Median Record - Lower Threshold of Likert Category) / Number of Records in this Likert Category
  • Create the following calculated field
Number of Values into Likert Category
Number of Values into Likert Category Test
Step 5:
  • Find the starting value of the Likert Category, i.e. the average of Likert Category and Likert Category - 1 = Likert Category - .5
  • Create the following calculated field
Start of Likert Category
Start of Likert Category Test
Step 6:
  • Take the Starting Value of the Median Likert Category, add the Number of Values into the Likert Category, i.e. Starting Value of Category + Number of Values into Category.
  • Apply this calculation only to the Median Likert Category (It is incorrect for every other category)
  • Create the following calculated field
Median Likert Score
Now, we can use see the final result of our work, the "Median" Likert Score for our data.
Final Result
I hope you found this informative.  Thanks for reading.


Brad Llewellyn
Associate Consultant
Mariner, LLC
brad.llewellyn@mariner-usa.com
http://www.linkedin.com/pub/brad-llewellyn/4a/80a/73b/
http://breaking-bi.blogspot.com

3 comments:

  1. Will this work if your dataset has multiple scales? For example, a survey that has some questions with a 1-5 response and other questions with a 1-9 response?

    ReplyDelete
    Replies
    1. If you have some way of slicing them, i.e. a dimension that takes values "Question 1" and "Question 2", then I see no reason why this wouldn't work. Feel free to experiment with it.

      Delete