2 Replies Latest reply: Aug 23, 2016 10:28 AM by Arvid Müllern-Aspegren RSS

    Average of values in N:th percentile

    Arvid Müllern-Aspegren

      One of the KPI:s that we trend with our Qlik dashbards, is an average of all values belonging to a certain percentile. The only way I've come up with of calculating this is very... verbose... It works fine, though.

       

      Does anyone have any suggestion on how this could be expressed in a more succinct manner?

      // General case: Return value is the average of the_value in the 5th percentile. 
      // Special case: For selections of less than 20 legs, return value is same as lowest the_value in selection.
      
      RangeMax(
            ((
            // Sum of the_value on legs where it is LESS than 5th percentile 
            sum(TOTAL  if( the_value < fractile(TOTAL the_value, 0.05), the_value))
            +
            (
                // Number of legs with the_value exactly equal to 5th percentile
                (
                    // Num legs in 5th percentile
                    Floor(count(TOTAL leg_id)/20)
                    - 
                    // Num legs with the_value less than 5th percentile value
                    (count(TOTAL if( the_value < fractile(TOTAL the_value, 0.05), the_value)))
                )
                * 
                fractile(TOTAL the_value, 0.05)
            )
             )
             /
             // Num legs in 5th percentile
             Floor(count(TOTAL leg_id)/20)
          ),
          Min(TOTAL the_value)
      )
      

       

      There was a question on an identical problem posted a couple of years ago. I used that solution initially, but realized that it gives incorrect results for selections with sequences of repeating values that touch the percentile boundaries. For certain cases, the error becomes very large and noticeable to end-users, so it is not an acceptable approach. The KPI is also calculated in other, non-Qlik-based, systems, and it is important that users get the same output in all systems, given they provide the same input!