
1. Re: How to create a calculation for "olympic average"
Adam Crahen Mar 21, 2016 1:13 PM (in response to J Frueh)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.

Olympic.twbx 25.6 KB


2. Re: How to create a calculation for "olympic average"
J Frueh Mar 21, 2016 1:54 PM (in response to Adam Crahen)Thank you very much for the reply Adam.
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"
Adam Crahen Mar 21, 2016 2:02 PM (in response to J Frueh)change "fixed" to "include"

4. Re: How to create a calculation for "olympic average"
J Frueh Mar 21, 2016 2:57 PM (in response to Adam Crahen) 
5. Re: How to create a calculation for "olympic average"
Adam Crahen Mar 21, 2016 6:50 PM (in response to J Frueh)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.

Olympic.twbx 38.3 KB


6. Re: How to create a calculation for "olympic average"
J Frueh Mar 22, 2016 8:33 AM (in response to Adam Crahen)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"
Adam Crahen Mar 22, 2016 8:39 AM (in response to J Frueh)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"
Adam Crahen Mar 22, 2016 8:42 AM (in response to Adam Crahen) 
9. Re: How to create a calculation for "olympic average"
J Frueh Mar 22, 2016 8:56 AM (in response to Adam Crahen)1 of 1 people found this helpfulWhat 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

10. Re: How to create a calculation for "olympic average"
J Frueh Mar 22, 2016 9:13 AM (in response to J Frueh)1 of 1 people found this helpfulI 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])) )