Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

arvid_m-a
New Contributor

Average of values in N:th percentile

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!

2 Replies

Re: Average of values in N:th percentile

Might be easier if we can test it out. Would you be able to share a sample?

arvid_m-a
New Contributor

Re: Average of values in N:th percentile

Thank you for replying!

Here is an example app with some demo data and the expressions I've used.

Community Browser