topic Top 10% function in QlikView

Top 10% function

Thu, 22 Apr 2010 23:43:34 GMT

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?

Top 10% function

johnw — Fri, 23 Apr 2010 00:26:21 GMT

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.

Top 10% function

Fri, 23 Apr 2010 00:44:20 GMT

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

Top 10% function

johnw — Fri, 23 Apr 2010 00:51:16 GMT

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.

Top 10% function

Fri, 23 Apr 2010 01:09:15 GMT

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

Top 10% function

johnw — Fri, 23 Apr 2010 18:10:33 GMT

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])