10 Replies Latest reply on Mar 22, 2016 9:13 AM by J Frueh

# How to create a calculation for "olympic average"

In case you're wondering, an Olympic average removes the high and low values in calculating the average.  For example:

I'd like to make a calculated field that does that.  What is the easiest way?

• ###### 1. Re: How to create a calculation for "olympic average"

Hi Jay-

I did it with some LODs

LOD

IF [Price] = {fixed : MIN([Price])} OR [Price] = {fixed: MAX([Price])}

THEN "Exclude" ELSE "Include" END

Olympic Avg

{fixed : SUM(IF [LOD] = "Include" THEN [Price] END)/COUNT(IF [LOD] = "Include" THEN 1 END)}

9.2 attached.

• ###### 2. Re: How to create a calculation for "olympic average"

However, I should have been more specific.  I need this calculated based on what is displayed, rather than what's in the table.  So the average needs to remove the largest and smallest values in the chart, not the largest and smallest in the whole database.

• ###### 3. Re: How to create a calculation for "olympic average"

change "fixed" to "include"

• ###### 4. Re: How to create a calculation for "olympic average"

Thank you again for the reply.

This does not seem to be working as expected.  As you can see by this screenshot, the result should be somewhere between 60 and 80 million.

However, this screenshot shows that the result is actually coming through as 197,824.  Not sure what I'm missing

• ###### 5. Re: How to create a calculation for "olympic average"

Hi J-

Hard to write calcs without seeing the whole picture.  Looks like you are trying to do the avg of a sum aggregation based on any field/filter in your view.

I changed the formulas a bit for this:

LOD Include

IF {include :sum([Price])} = {exclude [Year]: MIN({include [Year] :sum([Test])})}

OR {include :sum([Price])} = {exclude [Year]: MAX({include [Year] :sum([Test])})}

THEN "Exclude" ELSE "Include" END

Olympic Average

TOTAL(AVG({include : SUM(IF [LOD Include] = "Include" THEN [Price] END)/sum(IF [LOD Include] = "Include" THEN 1 END)}))

Attached a new sample with two amount per year showing how it works.  9.2.

• ###### 6. Re: How to create a calculation for "olympic average"

Thank you again Adam.  I appreciate that effort, but am I not then doing this by year?   The goal here is to have the olympic average be based on the current view.  Maybe it's the past 15 months, Maybe it's at the day level for the last 45 days.  I am trying to get the average of all the displayed values, without the largest and smallest.  Is LOD really the way to go about this?

• ###### 7. Re: How to create a calculation for "olympic average"

I don't have your data (or a sample) to work with so I can't give you a perfect solution.  I used the example from the link you provided.  Year is the dim I used.  You can use day or whatever is on your column shelf and modify the formulas.

• ###### 8. Re: How to create a calculation for "olympic average"

Another thing you could try is to sort you dimension ascending and then use a table calc:

WINDOW_AVG(SUM([Price]),first()+1,last()-1)

• ###### 9. Re: How to create a calculation for "olympic average"

What about doing this for the include part

if SUM([USDAmount__c])=window_max(sum([USDAmount__c]))

or SUM([USDAmount__c])=window_min(sum([USDAmount__c]))

then 'Exclude'

else "include"

end

If that would work, then I just need help with composing the last step

1 of 1 people found this helpful
• ###### 10. Re: How to create a calculation for "olympic average"

I got it.  Basically, I used and if statement saying that if the sum of my field IS NOT the window_max OR the window_min then do the window_avg

if (SUM([USDAmount__c])<>window_max(sum([USDAmount__c])) or SUM([USDAmount__c])<>window_min(sum([USDAmount__c])) )

1 of 1 people found this helpful