Complex Measures/Aggregations in Pivotgrid

6 posts, 0 answers
  1. Jens
    Jens avatar
    12 posts
    Member since:
    Aug 2014

    Posted 08 Oct 2014 Link to this post

    Dear Telerik team,

    is it possible to define more complex measures/aggregations than a simple sum for FLAT DATA?
    ... e.g. a calculating percentages, ratios, margins, etc. based on given datasource fields?

    Simplified Example:

    #these are the source data records:

    Product        Category                      SalesChannel    Costs    Revenue
    P1                 Mountain Bikes             Internet             2000    3000
    P2                 Mountain Bikes             Internet             1000    2000
    P3                 Mountain Bikes             Store                 3000    5000
    P4                 Mountain Bikes             Store                 5000    6000
    P5                 Accessoires                  Store                   100      150
    P6                 Accessoires                  Internet                 80      100
    P7                 Accessoires                  Store                     50        60
    P8                 Accessoires                  Internet                 10        15

    # We want to show the gross margin for each combination of category and sales channel with the formula:
          gross margin = (revenue-costs)/revenue

    # This should lead to the following pivotgrid matrix as result:
                                                                          All Sales Channels
                                                                          Internet         Store        All Sales Channels
    All Categories             Mountain Bikes             21,7%        28,6%        26,2%
                                       Accessoires                   40,0%        27,3%        31,3%
                                       All Categories                39,6%        27,3%        31,1%    

    Thanks a lot for your suggestions
    and best wishes from Germany
    Jens
  2. Georgi Krustev
    Admin
    Georgi Krustev avatar
    3707 posts

    Posted 09 Oct 2014 Link to this post

    Hello Jens,

    Currently, the measure function provides information only for the current value and accumulator. Hence only sum accumulation (reduction to be more precise) can be done. We will improve this for the upcoming RTM release of Kendo UI.

    As to the specific questions, could you please elaborate more, for instance, on the "Internet" measure calculation? Let's pretend that you have a dedicated column that holds the "gross margin" calculation. How do you expect to produce the 21,7% value for the Mountain Bikes if you are using the value from this additional column? In other words, you've got those items:

    Product        Category                      SalesChannel    Costs    Revenue  GrossMargin
    P1                 Mountain Bikes             Internet             2000    3000         0.3333
    P2                 Mountain Bikes             Internet             1000    2000         0.5

    How will the "21,7 %" value be calculated?

    As a side note, in term of performance, it will be better to calculate the Gross Margin values on the server using an additional column as it is faster than doing the same operation on the client.

    Thanks in advance for the clarification.

    Best regards,
    Georgi Krustev
    Telerik
     
    Join us on our journey to create the world's most complete HTML 5 UI Framework - download Kendo UI now!
     
  3. Kendo UI is VS 2017 Ready
  4. Jens
    Jens avatar
    12 posts
    Member since:
    Aug 2014

    Posted 09 Oct 2014 in reply to Georgi Krustev Link to this post

    Dear Georgi,
    thanks for the quick reply. You are right there was a mistake in the grid I accidently mixed the labels of the lines. Here the correct one:

                                                                         All Sales Channels
                                                                          Internet         Store        All Sales Channels
    All Categories             Accessoires                   21,7%        28,6%        26,2%
                                       Mountain Bikes              40,0%        27,3%        31,3%
                                       All Categories                39,6%        27,3%        31,1%

    However for ratios/percentages/margins you cannot calculate the measures on source record level (and therefore also not on the server).
    This would lead mathematically to wrong results in your aggregations later on.
    => So if you try to calculate a margin for each record of your data source and then aggregate via AVG then you will get a wrong value.

    The right way is the following (based on your question how to calc the margin for mountain bikes sold via internet):

    1. For each cluster/split get all the related records: 
        so get all records where Category=MountainBikes and Sales Channel = Internet

    2. For those records calculate: Margin = ( SUM(Revenues) - SUM(Costs) ) / SUM(Revenues)
            => which would be: 40% = (5000 - 3000) / 5000

    This is also the way how an OLAP server works, when you define an MDX formula like [margin]=([revenue]-[cost])/[revenue].
    It has simple measures, with simple aggregations like SUM/AVG/MIN/etc.
    And it has calculated measures built using certain simple measures.

    Something like this must be simulated for flat data. And it must be applied each time you do a drag & drop within the pivotgrid (create new break downs / combinations).

    Best wishes
    Jens

  5. Georgi Krustev
    Admin
    Georgi Krustev avatar
    3707 posts

    Posted 13 Oct 2014 Link to this post

    Hello Jens,

    Thank you very much for the clarification, as it helped to clear the situation out. We will further investigate the current implementation and limitations and will provide a better way to accomplish more complex calculations.

    Regards,
    Georgi Krustev
    Telerik
     
    Join us on our journey to create the world's most complete HTML 5 UI Framework - download Kendo UI now!
     
  6. Ants
    Ants avatar
    3 posts
    Member since:
    Dec 2012

    Posted 10 Nov 2014 Link to this post

    Hello, is there any update on complex aggregations and calculated fields?

    This pivotgrid is really nice visually and has some nice features but we don't have a remote data source, we have a fully client side js webapp.

    These are very fundamental features I think. For even the simplest of data sets, we need to be able to at least create a derived/calculated field such as (SUM(A) / SUM(B)) and then also be able to create semi-complex aggregates such as averages.

    Are there any workarounds for this? We will most likely have to use another product for now.

  7. Georgi Krustev
    Admin
    Georgi Krustev avatar
    3707 posts

    Posted 12 Nov 2014 Link to this post

    Hello,

    We are planing (and in fact we are working on it) to provide an additional dataitem and aggregate-context information to the aggregate function allowing to perform more complex calculations. This will be available either in the upcoming RTM release of Kendo UI PivotGrid or some of the next internal builds.

    Currently, I cannot provide any feasible workaround to accomplish the desired functionality.

    Best regards,
    Georgi Krustev
    Telerik
     
    Join us on our journey to create the world's most complete HTML 5 UI Framework - download Kendo UI now!
     
Back to Top
Kendo UI is VS 2017 Ready