3 Replies Latest reply on Dec 12, 2012 12:23 PM by Jonathan Drummey

# Counting populations by defined groups and display output as histogram

I have cumulative data for a list of individuals. How do I create groups and get a count for the population within each group and then get Tableau to produce a histogram displaying the distribution of the population? Ideally would compare previous year distribution with current year and also have a dynamic formula to define the size of the group depending on how far through the year you are, i.e. in month 2 you might split population into 10 hour groups, whereas in month 8 you might want it to be in 40 hour groups.

The attached file shows from data to graphical output the process in Excel that I am trying to get Tableau to perform. The data that the query would extract is in columns B, C & D. B would be the ID of the population and C & D are the values for 2 years 2011 and 2012. The formula in columns G & H converts by rounding up the values into "buckets" defined in size by the value in in H1.

The Max and Min in G3:H4 show the range of the data in the population and is used to set the range of the Graph data table in J8:L17. The formula in columns K & L is a count of the number of instances of the individual group values in columns G & H. The graph is a histogram of the Graph data table.

• ###### 1. Re: Counting populations by defined groups and display output as histogram

Hi Jonathan,

There are quite a few variables your described question that depending on how your data is set up may lead to several different ways of doing this. Would it be possible to post a sample workbook (twbx file)?

-Tracy

• ###### 2. Re: Counting populations by defined groups and display output as histogram

Tracy

Thanks for the reply. I have added an Excel file outlining what I am trying to achieve and showing the steps I go through to achieve the graph in Excel from the underlying data. I hope this helps as I am not upto speed on twbx files. Just starting off with tableau.

Jonathan

• ###### 3. Re: Counting populations by defined groups and display output as histogram

You can create a .twbx file by going to File->Export Packaged Workbook. This creates a Tableau workbook that has the data packaged within it.

For the view you wanted to create, the data was not optimally shaped as the Year is really a dimension and not a separate measure. I used the Tableau Data Reshaper add-in for Excel for this. There's more info on shaping data at http://kb.tableausoftware.com/articles/knowledgebase/preparing-excel-files-analysis, with a link to the reshaper.

The calculation you used to bin the data in Excel was a variation of a ceiling function, since Tableau doesn't have one native I built one, using ideas from here: http://community.tableau.com/thread/111530. Tableau's built-in bins use the next-lower value, and aren't able to be used in calculated fields (which is necessary to generate the grouped bars), so that's why I didn't use those.

Finally, to generate a grouped bar chart in Tableau we have to appropriately assign the Y axis values for each bar. The basic method is outlined in http://kb.tableausoftware.com/articles/knowledgebase/grouped-bar-chart.

See the attached, the last two worksheets were created using Tableau's built-in bins as an example.

Jonathan