your cart

How to do a simple likert scale chart

      There are many articles about to build a beautiful likert scale chart, such as: How to Visualize Sentiment and Inclination. In this article, the author use table calculation and gantt chart to get it. Some person may not be familar with that, So I will use another way to do it which is easier to understand on logic.
      The data is as below:
Product    Very Good    Good    Normal    Bad    Very Bad
A               86           78              55       15       5
B               75           69              45       38      12
C               44           55              36       29      75
      Usually, questionnaire score is from 1-5, average score will show on chart. We will make "Bad" and "Very Bad" as negative and put them on left side, the other 3 results will be on right side.
      After import to tableau, we need pivot data and change their name to "Result" and "Number".
      On the chart, we need use 2 score, one is for position which is from -2 to 2, the other is for showing which is from 1 to 5.
      Score (0) is used for split 0 point to 2 part:
CASE [Result]
WHEN 'Normal' THEN 1
END      
      Score (position):
CASE [Result]
WHEN 'Very Good' THEN 2
WHEN 'Good' THEN 1
WHEN 'Normal' THEN [Score (0)]/2
WHEN 'Bad' THEN -1
ELSE -2
END
      Score (Label):
CASE [Result]
WHEN 'Very Good' THEN 5
WHEN 'Good' THEN 4
WHEN 'Normal' THEN 3
WHEN 'Bad' THEN 2
ELSE 1
END
      Also, we need 2 average score for label and position.
      Avg Score(Position):
SUM([Number]* [Score (Position)])/SUM([Number])/5
      Avg Score(Label):
SUM([Number]* [Score (Label)])/SUM([Number])
      Then we need calculate Percentage of each result:
[Number]/{FIXED [Product]:SUM([Number])}
      Percentage need devided to negative and positive.
CASE ATTR([Result])
WHEN 'Very Good' THEN SUM([Number])/TOTAL(SUM([Number]))
WHEN 'Good' THEN SUM([Number])/TOTAL(SUM([Number]))
WHEN 'Normal' THEN SUM([Number])/TOTAL(SUM([Number]))
WHEN 'Bad' THEN -SUM([Number])/TOTAL(SUM([Number]))
WHEN 'Very Bad' THEN -SUM([Number])/TOTAL(SUM([Number]))
END
      Here, you also can use LOD calculation which is even more easier than table calculation. Exclude will let you get the total number regardless of each result.
CASE [Result]
WHEN 'Very Good' THEN [Number]/{EXCLUDE [Result]:SUM([Number])}
WHEN 'Good' THEN [Number]/{EXCLUDE [Result]:SUM([Number])}
WHEN 'Normal' THEN [Number]/{EXCLUDE [Result]:SUM([Number])}
WHEN 'Bad' THEN -[Number]/{EXCLUDE [Result]:SUM([Number])}
WHEN 'Very Bad' THEN -[Number]/{EXCLUDE [Result]:SUM([Number])}
END
      Now drag Product to Rows, Percentage to Columns, Result to Color.

      Percentage should be calculated using Result.

      Drag Average Score (Position) to Columns, then choose Dual Axis and Synchronize it.

      Make first chart as bar and only keep Result on Color.

      Make second chart as circle and put Avg Score (Label) on Label.

      After some format and color setting, we go the final chart. If you want show all positive percentage in Tooltip, you need create new calculated field without symbol "-"  for “Bad” and “Very Bad” only use for Tooltip.


Blog Category