5 Replies Latest reply: Apr 23, 2010 2:10 PM by John Witherspoon RSS

    Top 10% function

    BillGulledge

      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
          John Witherspoon

          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
              BillGulledge

              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
                  John Witherspoon

                  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
                  BillGulledge

                  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
                      John Witherspoon

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