1 2 Previous Next 19 Replies Latest reply on Nov 3, 2017 11:13 AM by Yingying Zeng

# Error using median function

I am unable to use the Tableau Window_Median function on an integer field and I do not understand why.  Here is my formula:

WINDOW_MEDIAN( [duration])

The duration field is straight from the db and is an INT with no NULLS.  The error I am getting is:

"All fields must be aggregate or constant when using table calculation functions or fields from multiple data sources."

• ###### 1. Re: Error using median function

WINDOW_MEDIAN(sum(duration))

Table calc arguments must be aggregated.

• ###### 2. Re: Error using median function

This does not make mathematical sense.  Summing the value before determining the median.  The median of the summed value is the summed value.

I used the median function above [  WINDOW_MEDIAN(sum(duration))  ] with the summed value and also displayed the MAX value for the duration as well [  MAX([Minimum Duration] )  ].  The median using this formula is higher than the actual max value found in the column.

I.E.:

Median value    :  533,514

Maximum value:    11,491

• ###### 3. Re: Error using median function

I'm just telling you how to eliminate the error; not suggesting any mathematics.  You must use an aggregation in a WINDOW function, that's all I'm sayin'--perhaps you should use

WINDOW_MEDIAN(MEDIAN(duration))

I don't know what you're trying to accomplish, and your original question was about resolving the error.  Whatever you are trying to accomplish, you must use an aggregated version of [duration] inside a WINDOW calc.

• ###### 4. Re: Error using median function

Hi Jason,

Have you tried MEDIAN([Duration]), this will calculate granular level.

• ###### 5. Re: Error using median function

There is no just MEDIAN function (I am using Tableau 8).  When I try that it does recognize MEDIAN as a keyword and turns it blue apparently, but give an error saying that there is no function called MEDIAN.

• ###### 6. Re: Error using median function

Certain data sources don't allow the MEDIAN function.  If you're using Excel, or one of the other sources that doesn't allow this aggregation, then you must Extract your data into Tableau first. (I believe it will show up then, but could be wrong).

If I am wrong, perhaps this will help: Calculating a Median with Raw SQL | Tableau Software

Extracting Data

Of course, you could also calculate this inside the data source.

• ###### 7. Re: Error using median function

I am using Tableau 8.0.2 and Median is available

• ###### 8. Re: Error using median function

JET data sources, like Excel and Access, do not support Median, which is why he cannot see it.  May want to look at this:

Calculating a Median with Raw SQL | Tableau Software

• ###### 9. Re: Error using median function

Yes, I am connected to a Tableau Extract and it is not available in excel.

• ###### 10. Re: Error using median function

I am using a live connection to MySQL.

• ###### 11. Re: Error using median function

Which also doesn't support the median function in Tableau--you have three options:

1) Calculate in the data source

2) Extract the data--median will be available then

3) Use a RAWSQL function, as described at the link below:

• ###### 12. Re: Error using median function

Thanks for the responses guys.  Where is the list of supported/unsupported data sources for median?  We are considering a switch to PostgreSQL as well.  I know natively, it also does not have a median function.

• ###### 13. Re: Error using median function

I was unable to find a complete list; but perhaps Russell Christopher or Robert Morton can comment on that.

Do you absolutely need a live connection?  An extract of your data inside Tableau will resolve this issue, and typically, performance is much better when using an extract.  You can set refresh schedules so that the data is nearly in real time, although you cannot get true real-time data with an extract, as far as I know.

• ###### 14. Re: Error using median function

No list, but Matt is correct. Functions like distinct count and median don't show up for JET (Excel, Access, Text) data sources. If you're using an extract against these sources, you can use them. If you didn't see the function then I suspect that while you may have "generated" an extract for the excel file, you weren't actually using it - you were still connecting live to the data source.

All the "real" databases (SQL Server, PostgreSQL, Oracle, etc.) support this stuff.

1 2 Previous Next