3 Replies Latest reply on May 28, 2012 7:30 AM by Jonathan Drummey

# Possible to calculate statistical mode across a spreadsheet?

Hi,

I currently have a spreadsheet that contains numerical values broken down by some dimensions.

The numerical values are not measures / countable. I require to find the statistical mode (most commonly occurring value) of these numbers while also being able to use filter conditions.

For example in the sheet:-

What is the statistical mode for a combined R(1,2,3) column?

What is the statistical mode for a combined R(1,2,3) column when only Chicago is considered?

There doesn't seem to be a function to calculate Mode directly, nor do sets allow me to combine the actual data into one dimension so I can calculate this.

Any ideas?

• ###### 1. Re: Possible to calculate statistical mode across a spreadsheet?

In other words (hopefully clearer!) is it possible to change a crosstab input to a vertical table in tableau itself, or will this need to be done at the datasource?

• ###### 2. Re: Possible to calculate statistical mode across a spreadsheet?

I would try to get the three columns into one, either at the source, or using custom SQL. Then it should be fairly straightforward.

1 of 1 people found this helpful
• ###### 3. Re: Possible to calculate statistical mode across a spreadsheet?

Hello Hari,

I used Alex's suggestion of Custom SQL in Tableau and a table calculation to find the mode. The Custom SQL was to UNION the original table together twice more, to create a combined R123 column.

From there, I did the following:

- Create a calculated field called Mode of R123 that just has the value [R123] and convert that to a Dimension. This is to have appropriate header text, and/or to allow using ATTR(R123) as a field in results.

- Create another calculated field called Mode Filter for R123 with the following calculation:

IF COUNT([R123]) = WINDOW_MAX(COUNT([R123])) THEN 1 ELSE 0 END

- Drag Mode of R123 onto the Rows Shelf.

- Drag Mode Filter for R123 onto the Filter Shelf. Set it to filter to 1, and set the Compute using to Mode of R123.

- Right-click on the City dimension and turn on Quick Filter.

You can see all this in the attached workbook.

One note - I saw that R1, etc. all used whole numbers. If you are using decimals, then you'll probably need to change the Mode of R123 calc to truncate or round in order to have an accurate bin size.

Cheers,

Jonathan

1 of 1 people found this helpful