Oct 122011
 

Overview

I received a number of comments from my last post (see http://www.datarevelations.com/likert-scale-nirvana.html) including what I think is a better approach for staggered Likert scale visualizations from Joe Mako.

Joe’s approach with various “kitchen sink” added functionality is shown in the interactive dashboard below.  Notice that you can control whether neutral values are displayed, sorting, Likert scores, and so on.

There’s some great stuff going on here but for this blog post I will focus on a simpler version of this visualization that just presents the staggered Likert scale bars without the various bells and whistles.

How the Viz Works

Let’s look under the hood of this simpler visualization (it’s the second tab in the workbook.)

Here we have Gantt bars (1) where the start position of the bars is determined by the table calculation Gantt Percent (2) and the thickness (or size) of the bars is determined by the table calculation Percentage (3).

Indeed, if you drag the Percentage pill off the Size shelf you’ll see more clearly how Gantt Percent dictates the position of the bars.

So, all we need to do is figure out how the two table calculations work.

To do this, let’s look at the third tab in the workbook where we present a cross tab view of the data with several intermediary calculations.  Both Tableau (and Joe) recommend building this type of view when first building table calculations.

Let’s look at all the Measure Values.

Number of Records

This SUM() function gives us the number of responses for each answer category (e.g.,  for the first question 9 people responded “Poor”, 17 responded “Fair”, 73 responded “Average”, and so on.)  We could also have used CNTD(ID) to determine these values.

Count Negative

We had to address this in the bar graph approach we used in the previous blog post and we’ll run into the same problem here as we need a way to signify that all the “Poor” and “Fair” responses should go to the left of the zero, along with half of the “Average” responses.  Our formula for determining this is

IF [Score]<3 THEN 1
 ELSEIF [Score]=3 THEN .5
 ELSE 0 END

Total Count Negative

Let’s look at the table calculation for this formula.

This tells Tableau to take the TOTAL of the SUM of all the Count Negative values addressing along the Answer field.

Note that in this case addressing across Answer is the same as Pane Down; however, it is much safer to use Answer as you can never be sure how the table construct may be changed as you explore different visualizations.

Total Count

This gives us the total number of responses for each question.

Note – from here on all Table Calculations in this example are computed along Answer.

Gantt Start

We will use this calculation to determine the left / right offset for the block of Gantt bars.  That is, for each question we have a bunch of bars that are stacked together and spread out horizontally.  This calculation will determine how far to the left or right of the center (0) the stack should start.

The formula we use is

-[Total Count Negative]/[Total Count]

Very simply, this is the percentage of responses that are negative.  Questions where most of the responses are positive (e.g., “Excellent:, Good”, etc.) will be close to the center (0); questions where most of the responses are negative will start much further to the left.

Now that we know where the stack of bars will start, we need to know where to position each of the Gantt bars (the bar for “Poor”, “Fair” etc.).  The Percentage table calculation will help is figure this out.

Percentage

This formula is very straightforward

SUM([Number of Records])/[Total Count]

Remember, we are addressing across Answer so this tells Tableau to add up the responses for a particular Answer and divide by the total number of responses for all possible answers; that is,

For Poor

9 / 417 = 2.2%

For Fair

17 / 417 = 4.1%

etc.

Incidentally, we will use this table calculation to determine the size (thickness) of the Gantt bars once we’ve determined exactly where to place them.  We determine the exact placement using Gantt Percent.

Gantt Percent

Here’s the formula

PREVIOUS_VALUE([Gantt Start])+ZN(LOOKUP([Percentage],-1))

This tells Tableau to look grab the previous Gantt Percent value (if there is no previous, for the first record in the partition, use Gantt Start value as the previous) then “lookup” the previous row’s Percentage value and add that to what you have.  If there is no “previous row” the ZN() function converts the NULL value to a zero.

Let’s see how this works with an example.

For the first row there is no previous Gantt Percent value, so we start with -15.0% (Gantt Start value).  We then “lookup” the previous Percentage value (which is null) so we get a zero.  This yields a result of -15% for Gantt Percent for the first record.

For the next row we use the previous Gantt Percent value (-15.0%) and then look at the previous row’s Percentage (2.2%). When we add these together we get -12.8%.

For the middle row (what would be the position for the “Average” bar) we start with the previous Gantt Percent value (-12.8%) and add the previous row’s percentage value (4.1%) yielding -8.8%.

Conclusion

Well, I hope this really is the conclusion as this is my fourth blog post about Likert scale visualizations.

I do like the staggered (or divergent) bar approach and will use it often.  I also prefer the Joe’s Gantt Bar method we explore here to the stacked bar approach I wrote about in http://www.datarevelations.com/likert-scale-nirvana.html.

But if I were to introduce other dimensions (e.g., region, age groups, time, etc.) I would probably just use a Likert Score bar and show the percentage breakdown using a tool tip like the one we discussed in http://www.datarevelations.com/a-little-more-on-likert-scale-questions.html.

 Posted by on October 12, 2011 2) Visualizing Survey Data, Blog 56 Responses »
Oct 062011
 

Overview

I’ve written a lot about this subject (see http://www.datarevelations.com/the-likert-question-question.html and http://www.datarevelations.com/a-little-more-on-likert-scale-questions.html) but some recent discussions with friends / colleagues Joe Mako and Naomi Robbins (along with some long plane rides to and from the Midwest) encouraged me to  see how difficult it would be to create a “staggered” Likert scale chart like the one shown below.

Actually, I came up with this approach several months ago but there were some “uglies” that I needed to work around.  Let’s look under the hood and see how this works and how to hide the “uglies”.

I’ve already addressed how to display the Likert score circles (which I love now more than ever) in a previous post so we’ll just focus on how to get the colored bars with emphasis on how to get the positive attributes to the right of the zero, the negative attributes to the left, and how to split the neutrals.

Measure Names

Formula for Dark Blue and Dark Orange Bars

Here’s the underlying formula for determining the number of responses that were Excellent.

This is pretty straightforward; just take the number of records where the score was 5 (Excellent) and divide by the total number of responses.

Here’s the formula for determining the number of responses that were Poor.

Same idea as before, but this time we add up all the “Poor” responses and make them negative.

Splitting the Neutrals

I’ve always preferred even-numbered Likert scale questions as you force people to take a stand as respondents cannot take the exact middle ground.

Not so with odd-numbered Likert scale questions, so we need to take all the neutrals and make half of them positive and half of them negative, as we do here:

It’s the same concept as with the Excellent / Poor responses but we divide by twice as many total responses to split the difference.

Dealing with the “Uglies”

There are at least three things that would prevent me from publishing this in its current form.

1)      Too many items in the legend.

(The “neutrals” are broken up into two groups.)

2)      Weird ordering of the legend.  To get the bars to stack properly I had to order the items in the legend as shown above.  Ugly.

3)      Color highlighting does not display amounts the way I would like.

This is the results of clicking S_Percent_Average_Neg in the color legend.  I only see half the neutrals and I see negative numbers.

The Hack (I mean “Solution”)

So, how can I display the values for each color bar and have a legend that does what I need it to do?

I “cheated” with the legend and am just using a static graphic, specifically, a screen shot of how I would like the legend to appear.  Go ahead and wrap your knuckles in it – It’s hollow.

As for allowing people to see the underlying values, I just applied “Cotgreavean” tool tip bars, as shown here.

So, I do wish I could just select all the neutrals and see their values, but I’m satisfied with this approach and like that the offsets help convey the positive / negative sentiment.

Please download and if you come up with a nicer way to handle the legend, I’d love to see it.

 Posted by on October 6, 2011 2) Visualizing Survey Data, Blog 11 Responses »