7 Replies Latest reply on Jul 3, 2019 6:23 AM by Ken Flerlage

# Mode Calculation

I have a field which is a time in seconds - I can use avg() as a calculated field but averaging times isn't great - I'd much rather find the mode - whats the best way to accomplish mode in tableau since there doesn't appear to be a mode() function ?

• ###### 1. Re: Mode Calculation

Are you able to provide some sample data?

• ###### 2. Re: Mode Calculation

currently I have three columns 'Date' which is a changeable param of Day, month, quarter, year. Then I have a unique identifier and then the Avg time column which utilizes my avg(timestamp) calculation

• ###### 3. Re: Mode Calculation

Using a very simple data set which just has a list of numbers from 1-10, we could do something like this to find the mode using two calculated fields:

Max Appearances

// Get the max appearances of a given number:

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

Is Mode?

// Is this number the mode?

// Note: Could be multiple modes.

IF {FIXED [Value]: SUM([Number of Records])} = [Max Appearances] THEN

"Yes"

ELSE

"No"

END The advantage of doing a Yes/No for the Mode is that your data set may have multiple modes. But, if you need just a single value, you could do something like the following. The biggest problem is that it doesn't account for the fact that there could be multiple modes and will just grab the smallest mode:

Mode

// Get the smallest mode

{FIXED : MIN(IIF([Is Mode?]="Yes", [Value], NULL))}

• ###### 4. Re: Mode Calculation

Where is [Max Appearances] field coming from ?

• ###### 5. Re: Mode Calculation

It's a calculated field that I showed above. It basically counts the number of records in which each value appears.

Max Appearances

// Get the max appearances of a given number:

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

• ###### 6. Re: Mode Calculation

Ahh ok - so I took my Time value and used datepart to get just the hours value yet when I go to calculate max appearances I get " you cannot use an aggregated expression as a dimension, because it is a measure"

• ###### 7. Re: Mode Calculation

Can you share a sample workbook?