3 Replies Latest reply on Oct 19, 2018 7:11 AM by swaroop.gantela

# Top 2 boxes and Likert scales

Hi All,

I have been closely following Steve Wexlers' excellent guide on how to visualise Survey data. I have come a long way in getting the Likert scale question into a divergent stacked bar chart (see attached workbook for all steps). And all looks pretty cool!

Now I want to add top2 box scores, or make a whole new bar chart with only top2 boxes across age brackets for example. Now this seemingly easy calculation of top2 boxes does not seem to work. I have tried a few different calculations and am now completely stuck.

Can anyone help in figuring this one out?

In attached workbook "Calc sheet for Likert" are all the variables used for calculating the Likert scale. At the right you can see what the top2 box calculation is adding up to: 100%?

Thanks a lot for your help!

• ###### 1. Re: Top 2 boxes and Likert scales

Arjen,

I think you will need a level of detail calculation to get the total.

The current calculation only calculates on a row level, but you are needingit calculated on the Statement level (I think).

You can try:

{ FIXED [Statement]:SUM( IF [Value]=1 OR [Value]=2 THEN 1 ELSE 0 END )}

/

{ FIXED [Statement]:SUM([Number of Records])}

Because some of the [Labels] are filtered out, I added filter to the context.

This could very likely also be done with table calculations.

1 of 1 people found this helpful
• ###### 2. Re: Top 2 boxes and Likert scales

Hi, I have one issue though with your calculation.

When you put location as a filter on the Top2 box, it doesn't add up correctly. So for example the top2 box for All countries is 91% (correct), but filtering on Africa still shows 91% (incorrect). I guess the {FIXED calculation does not allow to filter further?

Hope you can shed some more light on this.

Thanks

• ###### 3. Re: Top 2 boxes and Likert scales

Arjen,

There are two ways to incorporate other dimensions into the Level of Detail calculation.

You could add any such things that you are filtering with into the LOD:

{ FIXED [Statement], [Q0 Location] :SUM( IF [Value]=1 OR [Value]=2 THEN 1 ELSE 0 END )}

Alternatively, if you have many dimensions on the filter shelf,

you can just add them all to the Context: