Pth Percentile Calculation

[Jan]

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?

[Hinata]

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)