Weighted Average

6 posts, 0 answers
  1. Charles
    Charles avatar
    4 posts
    Member since:
    Oct 2014

    Posted 17 Jun 2015 Link to this post

    Hi,

    I need to understand how to implement a custom aggregate function which performs a weighted average. Or at the very least, a sum product. Ideally the user should be able to select both the item and then the item to weight it by.

    Is this even possible? If yes, can you please provide an example of how it can be achieved.

    I notice that the the official marketing spiel at WPF Pivot Grid mentions weighted averages within the Calculations section. However, it's not obvious to me how this can be done.

    Thanks in advance,

    Charles

  2. Kalin
    Admin
    Kalin avatar
    1208 posts

    Posted 18 Jun 2015 Link to this post

    Hi Charles,

    By using the Index calculation you should achieve the desired. You can apply it from the UI by clicking on the selected aggregate value in the PivotFieldList and selecting the Index item (check the screenshot). You can achieve the same from XAML by using the following snippet:

    <pivot:LocalDataSourceProvider.AggregateDescriptions>
        <pivot:PropertyAggregateDescription PropertyName="Net" AggregateFunction="Sum">
            <pivot:PropertyAggregateDescription.TotalFormat>
                <pivot:Index/>
            </pivot:PropertyAggregateDescription.TotalFormat>
        </pivot:PropertyAggregateDescription>
    </pivot:LocalDataSourceProvider.AggregateDescriptions>

    For more details please check the following article from our online help documentation (look for the Index calculation):
    http://docs.telerik.com/devtools/wpf/controls/radpivotgrid/features/localdatasourceprovider/calculations.html#calculations

    Hope this helps.

    Regards,
    Kalin
    Telerik
    Do you want to have your say when we set our development plans? Do you want to know when a feature you care about is added or when a bug fixed? Explore the Telerik Feedback Portal and vote to affect the priority of the items
  3. UI for WPF is Visual Studio 2017 Ready
  4. Charles
    Charles avatar
    4 posts
    Member since:
    Oct 2014

    Posted 18 Jun 2015 in reply to Kalin Link to this post

    Hi Kalin,

     Thanks for your response. However, this is not really what I am looking for. I need to perform a weighted average in terms of the aggregation itself. Not as a post-step calculation. I also need to select the item by which to weigh it.

     As an example, this could be achieved in SQL like so:

    select category, sum(item * weightItem) / sum(weightItem) as weightedAverage
    from tableName
    group by category

    In C# it could be implemented as an extension method. E.g.:

    public static double WeightedAverage<T>(this IEnumerable<T> source, Func<T, double> itemSelector, Func<T, double> weightSelector)
    {
        var result = source.Aggregate(Tuple.Create(0.0, 0.0), (a, t) => Tuple.Create(a.Item1 + (itemSelector(t) * weightSelector(t)), a.Item2 + weightSelector(t)));
     
        return result.Item1 / result.Item2;
    }

    Is there any way to somehow plug in this custom aggregation? 

    Also worth pointing out, I'm using a QueryableDataProvider even though the data I'm working against is an IEnumerable (I'm simply performing an AsQueryable). The LocalDataSourceProvider did not perform very well when I first tried it (it loads everything into memory).

    Thanks,

    Charles

     

  5. Kalin
    Admin
    Kalin avatar
    1208 posts

    Posted 23 Jun 2015 Link to this post

    Hi Charles,

    Can you please check this article and let us know if this is what you need to achieve (I'm also attaching a sample excel pivot demonstrating the solution)? Please note that the PivotGrid is designed to display only grouped, aggregated and calculated values of the underlying data - not the data itself. If you need to display the underlying data as it is and just calculate the weighted average that won't be possible.

    However if you can share some sample data and the expected result (a screenshot from example) we will be glad to further investigate the scenario and think of solution. Also if you can show what you need to achieve using the attached excel file, would be really helpful.

    I'm looking forward to hearing from you.

    Regards,
    Kalin
    Telerik
    Do you want to have your say when we set our development plans? Do you want to know when a feature you care about is added or when a bug fixed? Explore the Telerik Feedback Portal and vote to affect the priority of the items
  6. Charles
    Charles avatar
    4 posts
    Member since:
    Oct 2014

    Posted 30 Jun 2015 in reply to Kalin Link to this post

    Hi Kalin,

    Thanks for your help and the example Excel spreadsheet.

    I tried approaching the problem from a different angle and came up with a reasonable solution. It involved creating a calculated field in my datasource up front (gradeItem*weightItem) then applying a custom calculated field after the fact. In this particular case I created a class named WAvgCaclulatedField which inherits from CalculatedField. Within its CalculateValue method I'm able to obtain the aggregate value of both the initial calculated field and the weight. I then simply divide the first value by the weight and I have my weighted average.

    The only caveat is that the weighted average calculated field requires that both the item and the weight "be on screen" as fields. I.e. I can't hide them and only show the weighted average.

    But I'm happy with this result.

    Regards,

    Charles

  7. Kalin
    Admin
    Kalin avatar
    1208 posts

    Posted 01 Jul 2015 Link to this post

    Hello Charles,

    I'm glad you have managed to achieve the desired. However if are willing to share a sample project demonstrating the solution, we will be glad to check it and see if there is anything we can suggest in order to improve the implementation.

    If you have any other questions or concerns, please let us know.

    Regards,
    Kalin
    Telerik
    Do you want to have your say when we set our development plans? Do you want to know when a feature you care about is added or when a bug fixed? Explore the Telerik Feedback Portal and vote to affect the priority of the items
Back to Top
UI for WPF is Visual Studio 2017 Ready