Pth Percentile Calculation

2 posts, 0 answers
  1. Jan
    Jan avatar
    8 posts
    Member since:
    Sep 2014

    Posted 30 Sep 2014 Link to this post

    Here's my current scenario. I have 100+ records, grouped by one of their column values. Each detail record has a "Days Elapsed" integer value. I am trying to determine the value of Days Elapsed at the 90th percentile for each group. I can find what record I need, ie a group has 77 records, the record at the 90th percentile is the 69th. So what I want is to display the Days Elapsed value for the 69th record within that group.

    Is there any way to do this?
  2. Hinata
    Hinata avatar
    146 posts
    Member since:
    Dec 2013

    Posted 03 Oct 2014 in reply to Jan Link to this post

    Hi Jan,

    There is no such built-in function, but you can create a User Function to calculate the Pth percentile from a list of already sorted values:
    static public object GetPercentile(List<object> values, int percentile)
    {
        object result = null;
        var index = (int)Math.Ceiling(values.Count * (percentile / 100F)) - 1;
     
        if (index >= 0 && index < values.Count)
        {
            result = values[index];
        }
     
        return result;
    }

    or you can use a different implementation. Note that in order to provide the list of values, you will need to create a User Aggregate Function:
    public class StoreValues : IAggregateFunction
    {
        List<object> values;
     
        void IAggregateFunction.Accumulate(object[] values)
        {
            // Retrieve the parameter of our custom aggregate
            var value = values[0];
     
            this.AccumulateCore(value);
        }
     
        void AccumulateCore(object value)
        {
            this.values.Add(value);
        }
     
        void IAggregateFunction.Init()
        {
            this.values = new List<object>();
        }
     
        object IAggregateFunction.GetValue()
        {
            return this.values;
        }
     
        void IAggregateFunction.Merge(IAggregateFunction aggregateFunction)
        {
            var other = (StoreValues)aggregateFunction;
     
            foreach (var otherValue in other.values)
            {
                this.AccumulateCore(otherValue);
            }
        }
    }

    In the end the expression in your report will look like this:

    = GetPercentile(StoreValues(Fields.Amount), 90)
Back to Top