3 Replies Latest reply on Mar 16, 2017 6:18 AM by Katie Poznanski-Ring

# How to calculate components for Likert Scale Gantt Chart with tall data format?

Hi All,

I'm trying to build a Gantt bar chart to visualize some Likert scale survey data (following Steve Wexler's "Likert Scales - The Final Word?" post) and am running into an issue with some of the calculations.

In building the recommended crosstab (1st picture below), I noticed that the second column field, Count Negative, is not calculating correctly - it's over-counting each response because my data is in a tall format (multiple rows per respondent).

For example, in the first pane (2nd picture), my Count Negative should be 16 and 21 but it's calculating 64 and 84. This is based on CNTD(RespID); if I do CNT(RespID) then Count Negative matches. So, I need to find a way to tell Tableau to do a sort of CNTD(Count Negative), but I'm not sure how, because if I literally do that, I get all 1s. I'm thinking I need to write a function that combines MIN and IF like "IF MIN([RespID]) AND [Number Response] >=3 THEN 1 ELSE 0 END", but this results in errors...I'm pretty new at Tableau's functions, so I was hoping someone with much more function experience could make a suggestion?

How calculations should be running (from Steve's example)

How my calculations are incorrectly running (my data) Katie

• ###### 1. Re: How to calculate components for Likert Scale Gantt Chart with tall data format?

Update: Turns out I did my joins incorrectly. I first learned about reshaping survey data with the Tableau Pivot add-on to Excel through Steve Wexler's 2014 Tableau Conference presentation, then ultimately followed his updated instructions for reshaping within Tableau 10; however, I had the 2014 instructions in mind and unwittingly mixed the two methodologies, which resulted in a duplication of my demographic information, which is why the Count Negative field ended up multiplying everything by 4.

Solution: Only join 3 extracts (Value data, Label data, and Question Helper); a 4th Demographic extract is redundant and causes multiplicity issues.

• ###### 2. Re: How to calculate components for Likert Scale Gantt Chart with tall data format?

Katie,

I'm also trying to show survey data using Gantt bars, and have hit a snag that also happened when I tried to follow the previous postings on divergent Gantt charts.

I've reshaped the data, and created the "Cross Tab" so that I can see the calculation results: I'm using the formula from the blog post to calculate Gantt Start, but as you can see above, instead of getting -26% for the first question, I get -0.26, which is somewhat correct, but downstream calculations are wrong - apparently they don't recognize it as 26%, but as a small fraction.

Any ideas?

Thanks,

Robert

• ###### 3. Re: How to calculate components for Likert Scale Gantt Chart with tall data format?

Hi Robert,

Sorry for the late reply. I think it may be a data format issue; if the default isn't set to percent, Tableau returns the number format. Right click your Gantt Start measure --> Default Properties --> Number format --> Percentage should do the trick!

-Katie