2 Replies Latest reply on Oct 24, 2016 11:52 AM by Jennifer Young

# Calculate if an instance of a field is above or below average

Hey --

I am working on a project that involves visualizing and monitoring various different automated processes. As fields we have the category of process being run, start time, stop time, and the length of time the process ran. Each process runs once a day. I am being asked to visualize in a table using the words "over" or "under" next to each category of process whether the last run of that process was over or under average.

So, something like this:

Where what I'm trying to figure out what to do is get the last column together.

I tried creating a calculated field using an if statement.

I keep getting the following errors: Cannot Mix aggregate and non-aggregate arguments with this function.

Is there a different approach I could take here?

There has got to be something I'm missing here, or a simpler way to do this...

• ###### 1. Re: Calculate if an instance of a field is above or below average

The problem is that the AVG is an aggregating function that spans multiple rows but you are attempting to compare it to a single row.  In general Tableau does not like for the two levels of detail to mix; however, there is a level of detail function that will work for you.

A couple of options:

Create a calculated field containing {FIXED [Common Value] : AVG([Process run time])} and use that field in your comparison.  [Common Value] is a common value across which you want to do your average. Please note that the FIXED function looks at all rows and generally ignores most filters.

or

Use this function:  if SUM([Process run time]) > WINDOW_AVG(sum([Process run time]),FIRST(),LAST()) then "Above" else "Below" END   (you prob need a condition for equals as well).

Hope this helps.

1 of 1 people found this helpful
• ###### 2. Re: Calculate if an instance of a field is above or below average

Hey - sorry it took me so long to respond. Yes, this worked well! Both options had benefits.

Best,

Jen