Trimmed mean in Business Objects

This is a challenging one.

This is what the formula looks like:

But all this means is:

You pick a value k. And the % k number of values, is dropped while calculating the mean.

So, if k = 10% and you have 100 values, you'll be dropping the bottom 10 and the top 10 values and calculate the mean on the remaining 80.

I think it will be useful, to use ranking to calculate these.

First, lets calculate, from our list of products, with k = 30%, how many values we would need to drop.

We have 12 values. 30% of 12 = 4, so we'll be dropping the top 4 and the bottom 4 values.

Let's calculate this in Business Objects.

V_k = 0.3

=round(count([Lines]) * V_k,0)

which I will name V_ProductsToKeep

We need an integer of course, so round it is.

So, now we need to find out, what the 4th highest value is and filter everything out that is higher or equal. And we need to find out what the 4th lowest value is and filter everything out that is lower or equal.

=NoFilter(Rank([Sold at (unit price)];Top))

This gives us the Top  -- returning a value -- if V_ProductsToKeep is higher or equal than that value, we filter the value out.

Same with the lowest:

=NoFilter(Rank([Sold at (unit price)];Bottom))

This gives us the Bottom -- returning a value -- if V_ProductsToKeep is lower or equal, we filter that value out.

=If([V_Bottom] <= [V_ProductsToKeep]) Then (0) ElseIf ([V_Top]<=[V_ProductsToKeep]) Then 0 Else 1

Nice, now all we need is a formula, that determines what the sum is of [Sold at (unit price] where V_Evaluate = 1

=Sum([Sold at (unit price)])Where([V_Evaluate]=1) / ([V_NProducts] - ([V_ProductsToKeep]*2))

There. Not easy at all, but it works.

Our Trimmed mean with k = 33% is 166.6 -- higher than our regular mean, because we dropped the bottom and top 33% of the values.

If we change k to 0.1, the trimmed mean is:165.8

Good fun -- not that easy to do. I think that's going to be it for averages for now :)