Hitachi Vantara Pentaho Community Forums
Results 1 to 6 of 6

Thread: Aggregator "avg" doesn't work with function "Aggregate(<Set>)"

  1. #1

    Default Aggregator "avg" doesn't work with function "Aggregate(<Set>)"

    Hi!
    Seems that the Mondrian-Implementation of the MDX-function "Aggregate(<SET>)" doesn't work on a query with an 'avg'-measure, or I've overlooked something...

    I have a cube with some avg-measures and some max-measures.
    If I do a query like this:
    Code:
    with
    member [h].[selection] as   'Aggregate( {[h].[all].[1],   [h].[all].[3]} )'
    
    select
    {[Measures].[max_t_f], [Measures].[avg_t_f]}
    ON COLUMNS,
    {[chron].[whole time].[2007]})
    ON ROWS
    
    from [cubeName]
    where [h].[selection]
    , I get the following exception message in each JPivotTable cell of the 'avg'-measure:
    Code:
    #ERR: mondrian.olap.fun.MondrianEvaluationException: Don't know how to rollup aggregator 'avg'
    Is it my mistake or is there something wrong with mondrian?

  2. #2
    Join Date
    Nov 1999
    Posts
    1,618

    Default

    This is intended behavior. Mondrian can only aggregate measures based on count, min, max, sum. For other aggregate functions, such as avg, and a set of measure values, there's simply not enough information to rollup.

    Since it's not possible to rollup, you will need to compute the sum of all of the members in the set and divide it by the count of the members in the set.

    Julian

  3. #3

    Default

    Thanks Julian!
    I added a sum-measure, a count-measure and a calculated member with formula "sum / count" for each of my average-measures.
    Now it works!
    (I still don't understand why there is not enough information for aggregating avg-measures and I don't know what "rollup" means in that case, but it works... )

  4. #4
    Join Date
    Nov 1999
    Posts
    1,618

    Default

    Here's a puzzle for you: if I drive from A to B at an average speed of 30 mph, and from B to C at an average speed of 50 mph, what's my average speed for the whole journey?

    It's a trick question. There's not enough information to give an answer. You need to know how far it is from A to B and B to C, or how long each leg took, or something. Two numbers aren't enough.

    Those two numbers are all mondrian has.

    Mondrian could be super-smart and turn the expression inside out and recover the original sums and counts, but it doesn't. It's much easier if you rewrite the query, as you did.

    Julian

  5. #5

    Default

    Hm ... ok. Thanks for your explanation, Julian!
    I had a little problem with the fact, that the aggregation in the pivot table for example works very well, but not with the Aggregate-function.
    In essence, it's the same, I thought...
    Ok, no matter. I will understand the OLAP-thing, MDX and Mondrian better day by day (thanks to this forum). Maybe someday it will be absolutely clear for me!

  6. #6
    Join Date
    Nov 1999
    Posts
    1,618

    Default

    Logged feature request for this: http://jira.pentaho.com/browse/MONDRIAN-675

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Privacy Policy | Legal Notices | Safe Harbor Privacy Policy

Copyright © 2005 - 2019 Hitachi Vantara Corporation. All Rights Reserved.