4 Replies Latest reply on May 3, 2012 8:07 AM by Emma Whyte

# Best way to group large data set

Hello,

I have a data set of satisfaction questions on a 1-7 scale. We have created an Overall Satisfaction variable in our MySQL database that

is the sum of the coalesce() functions against 0 for each relevant field, divided by the sum of a count done against each of the same fields.

i.e. for each relevant field, if the field has a score in it the score is added up to form the numerator and 1 is added to build the denominator

However, this creates a problem when wanting to use this variable in Tableau.

I would like to create a bar chart with the 1-7 scale on the X axis and Count Number of Records on the Y axis.

The problem I am having is that Tableau plots every instance of a mean score between 1-7 that is possible on the X axis (please see picture attached). The bar gets taller the more people have the same mean score. Over time as more data is collected the bar graph would become a solid colour.

I would like to group the Overall Satisfaction variable, but would like advice on the best way to do this? I know how to create a group, but what would be the best way to group it?

I know social science data often comes across this problem and wondered if anyone else had come up with ways of solving it?

Thanks

• ###### 1. Re: Best way to group large data set

Hi Emma,

Would creating a group via a calculated field help? Something like:

if [Satisfaction]<=1 then '1'

elseif [Satisfaction]<=2 then '2'

elseif [Satisfaction]<=3 then '3'

etc...

etc..

end

Then, at least there would only be 7 different bars, although this could potentially make some of the bars way taller. I'm going to think about this one and see if I can come up with any other solutions.

-Tracy

• ###### 2. Re: Best way to group large data set

Hi Tracy,

Yeah I have thought about doing it that way, but like you say i'm stuck with how best to group the data together. I really don't want to do 1, 2, 3, 4, 5, 6, 7 as it won't give away any detail to the data.

Perhaps a bar chart isn't the best way to do this?

Any other solutions would be appreciated!

• ###### 3. Re: Best way to group large data set

Hi Emma,

A bar chart is an excellent way to display the "spread" of the data for something like this, so I think you're on the right track. What you call groups sounds like what I think of as bins. You might try Tableau's built-in binning functionality, you can set the bins to be whatever increments of the 1-7 measure you like. You can read more about bins here: http://onlinehelp.tableausoftware.com/v7.0/pro/online/en-us/calculations_bins.html

Alternatively, you can build your own bins using a calculated field. I demonstrated some different options in this post: http://community.tableau.com/message/176844#176844.

Jonathan

• ###### 4. Re: Best way to group large data set

Thanks Jonathan!

That's perfect. Tableau recommended a bin of 0.6, but after playing with the bin sizes 0.2 works perfectly. Gives a nice clear picture of the data.

Thanks again.