9 Replies Latest reply on Dec 5, 2018 9:39 AM by Paul Wachtler

# calculating the frequency of each number - Tableau 10.5

I am brand new to Tableau, This is an example from the book, 'Bigbook of Dashboards'. I created the table of numbers as it appears in the book. the column and row names are the names I chose to recreate this example. The objective is to create a sorted bar chart using color and length to show how many 9's are in the table.

Should a ranking function be used? I could write if then statements and create the values I need in SQL Server Management Studio, but I know Tableau can handle this more efficiently.

Message was edited by: laura Ashburn Can't seem to publish it. Will work with the suggestions, thanks

Message was edited by: laura Ashburn I attached the workbook with data source, I think

• ###### 1. Re: calculating the frequency of each number - Tableau 10.5

Hi Laura, I'm unable to open the workbook as it is not a packaged workbook. You might want to pivot the data first in order to count number of X's in the table. If this doesn't work try attaching a TWBX file.

• ###### 2. Re: calculating the frequency of each number - Tableau 10.5

Hi Laura,

.Twb files don't contain the data with them.  If you resave your file as a .twbx instead then the data will carry over.

Anyway, on your data source screen, select all 11 columns, right click, and select "pivot".  This will put all of your column headers into one field (call it whatever you'd like - maybe "Headers") and their values in another field (call it something like "Values").

Once you have the data pivoted, you can create a calculated field to count the number of 9s for each Header value - calling this field "9 Count" for this example:

sum(if [Value] = 9 then 1 end)

Drag your [Header] field onto columns.  Drop your [9 Count] field onto rows (make sure it's continuous).  Set your mark to bars.  Right click on [Header] and sort descending based on the [9 Count] field.

Let me know if you have any questions.

Best,

Paul

1 of 1 people found this helpful
• ###### 3. Re: calculating the frequency of each number - Tableau 10.5

I could get the results using pivot and bins

You will need to get n1 to n10 in same column using some pivot method in sql server in custom sql or view

1 of 1 people found this helpful
• ###### 4. Re: calculating the frequency of each number - Tableau 10.5

Something is amiss

• ###### 5. Re: calculating the frequency of each number - Tableau 10.5

I was able to get the number of records by Values

• ###### 6. Re: calculating the frequency of each number - Tableau 10.5

Hi Laura,

Not sure what you mean by something being amiss.  The count is working correctly to identify the number of 9s for each header.  The last piece you wanted was the sort - so if you right click on [Headers] and select sort - you can set it by descending by [9Count].

Let me know if there's another piece to this that I'm missing.

Paul

• ###### 7. Re: calculating the frequency of each number - Tableau 10.5

If you add up each individual bar they add up to 13 occurences of 9s, which matches your analysis.  Not sure what I'm missing here.

• ###### 8. Re: calculating the frequency of each number - Tableau 10.5

I see what you did, very cool. It is what I was asking for. I'm an infant in Tableau, so please pardon my inexperienced view of how to see things. Thanks very much! I learned a lot with this example

• ###### 9. Re: calculating the frequency of each number - Tableau 10.5

You're very welcome Laura!  Happy to help!