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!
