## 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.

Hi Steve

Interesting work. I like the focus on distribution for presentation of Likert ‘scales.’ The staggered approach also gives extra visual cues about the range of responses.

I have a minor quibble about using single questions, rather than 4 or 5 questions, to measure a concept. I presume you did so to avoid complexity. But if you use 4 or 5 questions you can the legitimately use Crombach’s alpha to test the significance of the response to that concept.

However, the sight of the Likert scores was like a red rag to a bull. I note that you provide it as an option to the more visually appealing presentation of the distribution but I still don’t like it. There are two reasons why I disagree with using and reporting Likert scores.

First, an ‘average’ or mean score, such as these, assumes scalar values, i.e. that the interval between 1 & 2 is the same as the interval between 2&3. However, one cannot assume the intervals between the values measured (‘poor’, ‘fair’, average’ good’ etc.) are scalar. So a mean score (as opposed to a ‘mode or a median’) is meaningless and misleading This issue will vary with the choice of words in your response scale and the cultures in which it is applied.

Second, a mean score assumes a normal distribution (most of the scores in one lump tailing off to the edges) [a mode score also makes the same assumption]. One can get responses which show a bi-modal distribution – two lumps at either end of the ‘scale’. It is much more important to know that views on this issue are polarised than to blandly report that the mean is “3.0”.

I liked the dataviz, and am impressed with your command of Tableau and the way you have used it for this example.

Except – like David – I found the average or mean score to be confusing. I agree with his assessment, but let’s for the moment assume that the mean is a meaningful calculation here – I think that it still may be misleading as presented.

It appears that you have coded your scale from 1 to 5 with 1 being the least positive and 5 being the most positive response and with 3 falling on the midline.

Then, looking at your first bar, the mean of 3.96 falls to the right of all of the bar (basically off of the bar itself). Visually, it is well to the right of even the responses coded 5.

For the final bar, the mean of 2.63 is depicted as falling somewhere within the responses coded as a 1.

Thus, the means appear to be using a different scale from the bars themselves which is confusing at best, misleading at worst.

But I also may be misreading the chart entirely.

Susan and David,

I encourage you both to see just how we got here and why you don’t need to apply linear values to the Likert scores.

Here are the various blogs that got us here:

http://www.datarevelations.com/the-likert-question-question.html

http://www.datarevelations.com/a-little-more-on-likert-scale-questions.html

http://www.datarevelations.com/likert-scale-nirvana.html

Susan, good point about how the mean scores extend beyond the scale. Have another look at the viz as I’ve adjusted the way this behaves.

Remember — you don’t HAVE to use the mean values but I find them very useful, especially if we were to add lots of other dimensions (e.g., age, gender, time, regions, etc.) In that case we sould have a sea of staggered Likert bars and it would be difficult to take it all in.

A final thought (for now) — some of my clients only care about the so-called “top two boxes” or “top box”. That is, they only want to see the percentage of people that selected “Good / Excellent” or “Excellent”. This troubles me, but I think it stems from not having a way to visualize the entire Likert scale and/or come up with an easy way to compare results for many questions.

Thanks both very much for the feedback.

I like the adjustment for the mean bubbles – for me it now reads more accurately using a scale parallel to the underlying Likert. For better or worse, the mean does provide a way of conveying the entirety of the data for the variable. With a well-constructed Likert scale, one in which the developer strove to render the intervals between response options conceptually equal, the mean represents an understandable (if not entirely statistically accurate) representation. It’s one of those things that generates a lot of argument, erring on the side of readability a bit over accuracy. I’m not against that – data is not fully meaningful, regardless of how accurate, if the general user can’t readily understand it and leverage it for decision-making.

This discussion was helpful…my thanks to everyone. I came across another resource that readers of this thread might find helpful: an R package called HH that includes a “likert” function to produce displays like those discussed above. Naomi Robbins calls this display a “diverging stacked bar chart”. http://www.amstat.org/sections/SRMS/proceedings/y2011/Files/300784_64164.pdf

Steve – with a little time on my hands I finally tackled this today, and WOW it’s perfect for my needs. Thank you so much for your in-depth exploration of this topic. I use Likert scales prodigiously in my work for clients, and am always looking for better ways to help them quickly grasp the meaning of a particular visualization. The staggered bars are such an easy way for them to see what’s going on, and I love (and always use) the color differentiation so they can see the variation in response counts for each scale option. The addition of the dual axis is great — and working through this taught me a variety of new tricks I hadn’t learned before!

As always, I’m so grateful for your generosity in sharing visualization help. Have you read my friend Jay Baer’s book “Youtility”? You should. You have it.

Grateful as always,

Susan

Susan, thank you for the kind words. When you get a chance have a look at http://www.datarevelations.com/data-visualization-explaining-just-what-it-is-that-i-do.html.

Kindest regards,

Steve

You’re so wonderful. Beers on me at TC14.

Steve,

Thanks for all of the great tutorials on visualizing survey data. I’m trying to create the chart above and was doing fine until I got to the Gantt Start. All I’m getting is zeros – same with Percentage. I can’t figure out what I’m doing wrong. I’ve checked and rechecked the formulas to make sure they are correct and that I’m calculated across Answer (called Satisfaction in my dataset). Any ideas??

Best regards

Deb Duffy

Deb,

I suspect the things that are zeros just *look* like zeros. Try formatting those fields so they are percentages with one decimal place. If that does not do the trick, let me know and we can do a screen share and try to figure out where the problem is.

Steve

Nevermind – I found the problem! [facepalm]

You were right – that was exactly the problem

I love the visualization in the second tab, which I’ve used for all the Likert-scale questions in my survey.

In fact I like it so much I want to be able to keep it as a “template.” Is there a way I can ignore my current data set and just bring a new survey sample (with the same measure and dimensions) so I can use the same parameters, etc., every time?

I do a lot of surveys with 5-scale questions like this, so that would be really useful.

thanks for any suggestions!

Tom,

Not as easy as I would like it to be. You can either copy and paste all the relevant fields from one workbook to another, or you can replace one data source with another.

When you do the latter, things will “work”, but all the color assignments will change to Tableau’s defaults. I know that Kelly Martin has done some work in this area with creating parameter-driven templates where you can swap data sources.

Steve

thanks a lot, Steve.

Now I’m stuck at being able to Replace Data Source, since that option is grayed out when I try to choose it from the Data menu or by right-clicking it from the sidebar.

never mind–kind of a basic question, and I found the answer:

http://kb.tableau.com/articles/knowledgebase/replacing-data-source

When I save my twb as a twbx all the pills on the filter shelf disappear. Is there any way to keep that shelf so I can share that functionality with Tableau Reader users–so they can have a set visualization defaulting to “all responses” but then select & deselect some based on the category filters I’ve created?

thanks for any ideas

Think I found a good solution–by using quick filters, which do appear in Reader files. Any other ideas welcome too.

Hello,

Have just across this extremely helpful article.

Was hoping to use the your Tableau Workbook Format to create Likert scales for my data, but am having difficulty with the format of the Excel spreadsheet. Is there any chance that you could show me the Excel datasheet you used to create the Likert scales above?

Would be grateful for any kind of help!

Tiffany,

Glad to hear the post was useful. May I suggest going to

http://www.datarevelations.com/visualizing-survey-data

and downloading the packaged workbook and the source file? It will explain how to get the data set up and you can just copy and paste the formulas from the my workbook into yours.

You can also watch a video from last year’s Tableau Conference that goes into getting the data setup (see https://www.youtube.com/watch?v=MbTKbghfzcA&feature=youtu.be).

You may also find these posts useful: http://www.datarevelations.com/reshaping-survey-data-with-tableau-9-0.html and http://www.datarevelations.com/alteryx_survey_part1.html

Steve

Thanks for trying to help Steve! I tried, but didn’t manage with Tableau. Ended up drawing the graph with different software, but appreciate your advice all the same!

Hi Steve,

This article was extremely helpful; thank you very much for putting it together. In your previous article, I really enjoyed the use of the bar chart displaying within the tooltips. I’m very new to Tableau, but is there a way to add that functionality in with this method? I’ve been trying for hours and can’t seem to get it right. Thank you!

Michael,

What you are looking for is a way to get a bar chart in a tooltip. In an upcoming version of Tableau you’ll be able to do this easily, but here is a link to how you can do it now:

https://public.tableau.com/s/blog/2010/09/using-bar-charts-tooltips

and

https://public.tableau.com/s/blog/2014/04/using-bar-charts-tooltips

Steve

Thank you very much, Steve. This is exactly the process I am trying to replicate. However, I am having the issue where my tooltip only displays a bar for 100% for the segment of the bar chart I am currently hovering over.

Do you have any suggestions for how to make the bar chart display the breakdown for the entire question?

I downloaded your workbook for the “likert scale nirvana” post and tried to replicate that tooltip process with the staggered, gantt visualization you discuss here. However, so far I have been unsuccessful.

The issue I am having is that in your previous example, right-clicking on any segment of the bar chart and viewing the underlying data shows all surveys and all answer choices. Thus, the formulas you used work, since they pull from that underlying data which includes everything.

When I follow the method in this post, the underlying data only seems to include the responses for each answer category. For example, if 30 people in my survey selected “Excellent” for Question 1, if I view the underlying data for Excellent for Question 1, I only see 30 responses. Before, I would have seen the entire population.

Is there no good way to make these tool tips work with this post’s method?

Is this method still the expected workflow in modern versions of Tableau? I am curious to know if any additional tools or methods have been released for achieving similar outcomes.

Naeem,

When you say “expected workflow” do you mean getting the data set up properly, or building the table calcs so you can have the divergent stacked bar chart?

If the former then there are lots of ways to get your data in the right format and you’ll find them on the “Visualizing Survey Data” page. If you mean is there an easier way to get the divergent bars, I don’t think there is… but…

It’s very easy to just copy the calcs that you need from the various sample packaged workbooks into your own workbooks and then modify the calculations to fit your data.

Steve

Not sure if this is what Naeem was getting at, but as I started to build this in Tableau 10, my first instinct was to use LOD calcs instead of table calcs. Which, worked great until the Gantt Percentage field. When I try to fix that particular calc to the survey question, I get an ” LOD expressions cannot contain table calcs or the ATTR function.” which I think is happening because the percentage is an ATTR.

Anyway, at this point, I’m planning to download the sample packaged workbooks to get it working with table calcs exactly as it But I’m curious to know, do you think that this viz could be done with LOD expressions instead? Or are table calcs still the “correct” solution for this?

Looks like me and that Gantt Percent field are not getting along, getting a different error when I copy the calcs from the packaged workbook, “Argument to SUM (an aggregate function) is already an aggregation, and cannot be further aggregated.” Bah, I’ll spend more time on it this weekend, but would still love to hear your thoughts on using LOD calcs, (and would welcome any leads on why I’m getting this aggregate function error ).

Tracy,

I think this is definitely still a “table calc” thing.

What I usually do is copy the six calculated fields I need from the sample packaged workbook into my new workbook. The six being these:

Count Negative

Gantt Percent

Gantt Start

Percentage

Total Count

Total Count Negative

Now, when you do this you’ll probably see lots of red exclamation points because one or more of the things you copied refer to fields that may not be in your data set. For example, “Count Negative” may refer to a field called “Value” when you have a field called “Score”.

Also, the “Compute Using” settings for the table calcs will probably need some adjustment.

Steve

Just wanted to follow up and say thank you so much for this post and for being so responsive! It’s community members like you that make Tableau so great!

Steve,

This is great stuff!! I have a question about sort of “subcategories” or “nesting” results with this solution. We have a bunch of survey questions and each question is grouped into a characteristic (a dimension within the data). So we’ll have likert values for the individual survey responses to that question and then each of the questions within that characteristic will then be aggregated and we show the likert values for that characteristic. Right now, I have 2 vizzes side by side: one with the characteristics and another with the questions and as you click on a characteristic the questions list is filtered down.

What I’d love to do to is cut down on space and “nest” those results like the classic category / sub-category examples of hierarchies you see out there so you click on characteristic #1 which then expands the viz to show each of the questions within that characteristic. I created the heirarchy and it shows up in the characteristics viz but then when I expand it out it aggregates at the likert value so it groups all of the 5’s for each question and then the 4s and so forth rather than each question displaying a likert scale.

Is what I’m describing possible with this solution? If so, any tips on how I might implement that?

Thanks, love the blog!!

Bryan

Bryan,

If I understand correctly it sounds like you have a several collections of Likert-scale questions that roll up into a summary visualization. That is, you may have a viz that summarizes Q1-5, another that summarizes Q6-13, another that summarizes Q14-20, and so on.

When you click one of these summary questions you want to see some type of expansion that shows the results for the individual questions. You may want to go even further and see if for the individual questions there’s a difference among men and women, where people live, etc.

If I understand this correctly, the easiest way to handle this would be dashboard actions. Very easy to set up.

Steve

That’s correct. I’m looking take a category like “Feedback on Teachers” which contains 5 questions throughout the survey or “Feedback on Curriculum” which maybe contains 4 questions throughout the survey and have those categories show up on the dashboard showing the aggregate values for all questions contained within those categories and when selected they in effect expand out to show each of the individual questions within that category. And I have various filters to select gender and such like you mention. Guess I’d never thought to use just the one viz with filter actions to change from categories to questions, I’ve always laid it out side by side as there’s still some value in showing ALL questions…but I’m running into major space problems and need to figure out how to consolidate the categories and questions into a single viz, hence my looking for that “cascading” solution where the categories expand when selected

Thanks for the reply Steve and keep up the fantastic work; it’s a huge help to me and tons of others!

Bryan

Hi Steve-

For some reason, I cannot get my middle/’average’ responses to split across the 0. They go to the left of the 0 along with my poor and very poor responses. My response values are 0-4 instead of 1-5, but that shouldn’t make a difference. Any suggestions?

Rien,

I use 0 – 4 scale in my class examples. I assume you are using < =1 for negative, 2 for Neutral, and >=3 for positive. Have you moved things around in your color legend? The color legend needs to be in the correct order.

Steve

Yes, this was the problem. Thank you!

Oh, never mind. I am a moron. It works!

I presume that this will also leave me confessing to being foolish, but here goes…. I am displaying Likert responses (1-5, plus “not applicable” as 0, which I believe is the most meaningful way to account for it in the context of the survey).

The categories that skew towards negative responses do not display any positive responses. The entire bar is on the negative side (shows segments 0-3), and there are no segments for the positive values (4, 5) even though there are positive responses. The neutral response is not centered on the line at 0 for Gantt Percent for any of the questions.

I am using Tableau 10 desktop.

Thank you,

Rachael

Rachel,

Very reasonable question.

I would create two visualization side by side. The first would be the divergent stacked bar chart, but I would filter out all the 0 responses.

The second chart would be the “not applicable” responses, assuming showing this is meaningful to you.

Make sense?

Steve

Thank you for your response, I’m not sure that I’ve done a good job of explaining the problem. Filtering out the 0 responses does not change the issue of not seeing the 4 or 5 responses. I’ve entered a URI with an image of what I’m seeing.

Thanks,

Rachael

Rachael,

Indeed, something clearly is not correct. Are you able to send me the packaged workbook and I can have a look? If not, perhaps we set up a screen sharing session and we can see just what’s up with the data?

swexler@datarevelations.com.

It turns out that Compute Using is important Thank you for the help!

Rachael

Hi Steve,

Thanks to this in-depth article of yours I was able to create a Gantt Combo Chart for course evaluation data. My dean loves it but would also like to see it as a cross tabulation / summary table with the Likert values and mean values side by side for each course taught by each unique instructor (each instructor would get their own summary sheet); similar to the tutorial’s 4th image, but each instructor’s course would be listed as you listed Likert labels and the Likert labels + mean values would run across the top (as separate variables) similar to how the Measure Values run across the top.

I tried duplicating the chart as a crosstab, but it produces two separate sheets instead of one summary table. I’m not sure what else to do…any advice?

Thanks for all you do!

Katie

Katie,

Yes, duplicate as crosstab is a terrific technique but in this case it will create two separate sheets. The trick (and it’s easy) is to just add more measure values to one of the tabs it creates.

You get a crosstab using Tableau’s measure names / measure values construct. Tableau “guessed” which values you wanted to see. You can override this by removing some measures and adding others.

If you are stuck let me know and we can do a quick screen share.

Steve

Steve,

Thank you for your quick reply! I started a thread in the Tableau Community Forums with this question as well, and your advice was echoed. I was able to display the Grand Means and Grand Totals for each course, but I’m still having trouble with hiding the means that appear under each Likert item. I would certainly appreciate it if you could take a look!

Here is the link to the thread where I have a screenshot of what I am trying to achieve as well as a dummy workbook: https://community.tableau.com/message/565370#565370.

Many thanks,

Katie

Katie,

I replied on the forum and have uploaded a workbook that I believe does what you want.

Steve

Admittedly a novice (supervisor saw this blog and asked me to try to get our data to be displayed as Gantt), using Tableau 10.1.5, and having what I hope is minor problem.

Everything was going fine until I tried to calculate Gantt Start. The result that I get in my Cross Table is not shown as, for example, 26%, but as a decimal – 0.26, which of course distorts all downstream calculations.

Any ideas would be appreciated.

Thanks,

Robert

Robert,

Are you just trying to replicate the example?

If yes, you should download the open the embedded workbook and see what is different about it than what you have. For example, do you have the label elements in the same order (Poor, Fair, Average, etc.)?

Steve