Top 10% function

[#1]

In an expression, "AVG" gives me the average of the filed values defined by the expression across all the records selected. I don't want the average, I want the average of the maximum 10%. How do I get this? For example, if the records consist of name and weight, the "AVG" function gives me the average of all the weights for the records selected. How do I get the average weight of the 10% of the selected records that hre the heaviest?

[johnw]

Untested, but something like this?

avg(<{Weight*={">=$(=fractile(Weight,.9))"}>} Weight)

The fractile(Weight,.9) should find a Weight where 90% of your selected records are lighter, and 10% are heavier. We use that inside a search expression, ">=...". The search expression is then applied to the Weight column using set analysis to find ALL Weights equal to or greater than that Weight. But not all of those are necessarily selected, and we only want the selected Weights, so we intersect that set with our selections using *=. Finally, we take the average Weight of the results. Sounds right in my brain. Not sure if I have all the syntactic details correct, though.

[#3]

Thanks John, not only for the code but especially for taking time to pass along your thought process and the explanation.

Will this still work if the variable "weight" in the expression is replaced with another expression using several variables?

Bill

[johnw]

Unfortunately no, not as a mere substitution, anyway. You can only use set analysis on fields, not on expressions, I believe. It doesn't mean all is lost; it just means that we may have to rearrange some things. Let me know what you're really trying to do, and maybe I can figure it out.

[#5]

Not to wear out my welcome, but I can't see the problem here

avg({$<[Provider-Name]=>},{$<[[Provider-Id]]=>} ,

(<{time to bill*={">=$(=fractile(time to bill,.9))"}>} time to bill))

In calculating this expression, I want to ignore any selections on the variables [Provider Name] and [Provider Id]; I want the average of the field [time to bill] for the top 10% of the of the records with the highest [time to bill]

Don't know what happened to my font - sorry

[johnw]

Syntax problems. I think you want this, but it's getting complicated enough that I'm unlikely to be entirely correct.

avg({<[Provider-Name]=,[Provider-Id]=,[time to bill]*={">=$(=fractile({<[Provider-Name]=,[Provider-Id]=}>} [time to bill],.9))"}>} [time to bill])