Hitachi Vantara Pentaho Community Forums
Results 1 to 3 of 3

Thread: [Mondrian] VisualTotals issue.

  1. #1
    Ati Rosselet Guest

    Default [Mondrian] VisualTotals issue.

    Luc/Julian,

    Using VisualTotals(), somehow the Time dimension (at least) is not being
    handled correctly wrt aggregates.
    Visual Totals sums up the values across its elements, but in the case of an
    aggregate (e.g. time rollup), it should be summing up the underlying values
    rather than the cell values
    (if that makes sense).

    I've reproduced the problem with 2 Foodmart queries to view
    "sales to men for 1997, and by quarter, and a roll-up to show sales to
    men cumulated over the year" (I'm not a male chauvinist, this just was the
    first that came to mind )

    with
    member [Gender].[YTD] as 'AGGREGATE(YTD(),[Gender].[M])', format_string =
    "#,###00.0"
    select
    {[Time].[1997],

    [Time].[1997].[Q1],[Time].[1997].[Q2],[Time].[1997].[Q3],[Time].[1997].[Q4]}
    ON COLUMNS,
    {[Gender].[M],[Gender].[YTD]} ON ROWS
    FROM [Sales];

    returns (correctly):
    Axis #0:
    {}
    Axis #1:
    {[Time].[1997]}
    {[Time].[1997].[Q1]}
    {[Time].[1997].[Q2]}
    {[Time].[1997].[Q3]}
    {[Time].[1997].[Q4]}
    Axis #2:
    {[Gender].[M]}
    {[Gender].[YTD]}
    Row #0: 135,215
    Row #0: 33,381
    Row #0: 31,618
    Row #0: 33,249
    Row #0: 36,967
    Row #1: 135,215.0 <--- [Time].[1997] is aggregated correctly
    Row #1: 33,381.0
    Row #1: 64,999.0
    Row #1: 98,248.0
    Row #1: 135,215.0

    whereas:

    with
    SET [VTime] AS 'VisualTotals({[Time].[1997],[Time].[1997].Children})'
    member [Gender].[YTD] as 'AGGREGATE(YTD(),[Gender].[M])', format_string =
    "#,###00.0"
    select
    {[VTime]} ON COLUMNS,
    {[Gender].[M],[Gender].[YTD]} ON ROWS
    FROM [Sales];

    gives:
    Axis #0:
    {}
    Axis #1:
    {[Time].[1997]}
    {[Time].[1997].[Q1]}
    {[Time].[1997].[Q2]}
    {[Time].[1997].[Q3]}
    {[Time].[1997].[Q4]}
    Axis #2:
    {[Gender].[M]}
    {[Gender].[YTD]}
    Row #0: 135,215
    Row #0: 33,381
    Row #0: 31,618
    Row #0: 33,249
    Row #0: 36,967
    Row #1: 331,843.0 <<<< this should be 135,125 as far as I can tell
    Row #1: 33,381.0
    Row #1: 64,999.0
    Row #1: 98,248.0
    Row #1: 135,215.0

    note axis #2 and axis #2 are identical in both cases, so I'm assuming it has
    to do with the internal handling of VisualTotals...

    any ideas - or should I just create a testcase for this and open a new JIRA
    issue?
    Cheers
    Ati

    _______________________________________________
    Mondrian mailing list
    Mondrian (AT) pentaho (DOT) org
    http://lists.pentaho.org/mailman/listinfo/mondrian

  2. #2
    Luc Boudreau Guest

    Default Re: [Mondrian] VisualTotals issue.

    Ati,

    Please log this in Jira. Someone will look into it and determine if this is
    a bug.

    Thanks for reporting this!

    Luc

    On Wed, May 11, 2011 at 7:19 AM, Ati Rosselet <ati.rosselet (AT) gmail (DOT) com>wrote:

    > Luc/Julian,
    >
    > Using VisualTotals(), somehow the Time dimension (at least) is not being
    > handled correctly wrt aggregates.
    > Visual Totals sums up the values across its elements, but in the case of an
    > aggregate (e.g. time rollup), it should be summing up the underlying values
    > rather than the cell values
    > (if that makes sense).
    >
    > I've reproduced the problem with 2 Foodmart queries to view
    > "sales to men for 1997, and by quarter, and a roll-up to show sales to
    > men cumulated over the year" (I'm not a male chauvinist, this just was the
    > first that came to mind )
    >
    > with
    > member [Gender].[YTD] as 'AGGREGATE(YTD(),[Gender].[M])', format_string =
    > "#,###00.0"
    > select
    > {[Time].[1997],
    >
    > [Time].[1997].[Q1],[Time].[1997].[Q2],[Time].[1997].[Q3],[Time].[1997].[Q4]}
    > ON COLUMNS,
    > {[Gender].[M],[Gender].[YTD]} ON ROWS
    > FROM [Sales];
    >
    > returns (correctly):
    > Axis #0:
    > {}
    > Axis #1:
    > {[Time].[1997]}
    > {[Time].[1997].[Q1]}
    > {[Time].[1997].[Q2]}
    > {[Time].[1997].[Q3]}
    > {[Time].[1997].[Q4]}
    > Axis #2:
    > {[Gender].[M]}
    > {[Gender].[YTD]}
    > Row #0: 135,215
    > Row #0: 33,381
    > Row #0: 31,618
    > Row #0: 33,249
    > Row #0: 36,967
    > Row #1: 135,215.0 <--- [Time].[1997] is aggregated correctly
    > Row #1: 33,381.0
    > Row #1: 64,999.0
    > Row #1: 98,248.0
    > Row #1: 135,215.0
    >
    > whereas:
    >
    > with
    > SET [VTime] AS 'VisualTotals({[Time].[1997],[Time].[1997].Children})'
    > member [Gender].[YTD] as 'AGGREGATE(YTD(),[Gender].[M])', format_string =
    > "#,###00.0"
    > select
    > {[VTime]} ON COLUMNS,
    > {[Gender].[M],[Gender].[YTD]} ON ROWS
    > FROM [Sales];
    >
    > gives:
    > Axis #0:
    > {}
    > Axis #1:
    > {[Time].[1997]}
    > {[Time].[1997].[Q1]}
    > {[Time].[1997].[Q2]}
    > {[Time].[1997].[Q3]}
    > {[Time].[1997].[Q4]}
    > Axis #2:
    > {[Gender].[M]}
    > {[Gender].[YTD]}
    > Row #0: 135,215
    > Row #0: 33,381
    > Row #0: 31,618
    > Row #0: 33,249
    > Row #0: 36,967
    > Row #1: 331,843.0 <<<< this should be 135,125 as far as I can tell
    > Row #1: 33,381.0
    > Row #1: 64,999.0
    > Row #1: 98,248.0
    > Row #1: 135,215.0
    >
    > note axis #2 and axis #2 are identical in both cases, so I'm assuming it
    > has to do with the internal handling of VisualTotals...
    >
    > any ideas - or should I just create a testcase for this and open a new JIRA
    > issue?
    > Cheers
    > Ati
    >
    > _______________________________________________
    > Mondrian mailing list
    > Mondrian (AT) pentaho (DOT) org
    > http://lists.pentaho.org/mailman/listinfo/mondrian
    >
    >


    _______________________________________________
    Mondrian mailing list
    Mondrian (AT) pentaho (DOT) org
    http://lists.pentaho.org/mailman/listinfo/mondrian

  3. #3
    Ati Rosselet Guest

    Default Re: [Mondrian] VisualTotals issue.

    JIRA issue opened :
    http://jira.pentaho.com/browse/MONDRIAN-939

    On Wed, May 11, 2011 at 4:37 PM, Luc Boudreau <lucboudreau (AT) gmail (DOT) com> wrote:

    > Ati,
    >
    > Please log this in Jira. Someone will look into it and determine if this is
    > a bug.
    >
    > Thanks for reporting this!
    >
    > Luc
    >
    > On Wed, May 11, 2011 at 7:19 AM, Ati Rosselet <ati.rosselet (AT) gmail (DOT) com>wrote:
    >
    >> Luc/Julian,
    >>
    >> Using VisualTotals(), somehow the Time dimension (at least) is not being
    >> handled correctly wrt aggregates.
    >> Visual Totals sums up the values across its elements, but in the case of
    >> an aggregate (e.g. time rollup), it should be summing up the underlying
    >> values rather than the cell values
    >> (if that makes sense).
    >>
    >> I've reproduced the problem with 2 Foodmart queries to view
    >> "sales to men for 1997, and by quarter, and a roll-up to show sales to
    >> men cumulated over the year" (I'm not a male chauvinist, this just was the
    >> first that came to mind )
    >>
    >> with
    >> member [Gender].[YTD] as 'AGGREGATE(YTD(),[Gender].[M])', format_string =
    >> "#,###00.0"
    >> select
    >> {[Time].[1997],
    >>
    >> [Time].[1997].[Q1],[Time].[1997].[Q2],[Time].[1997].[Q3],[Time].[1997].[Q4]}
    >> ON COLUMNS,
    >> {[Gender].[M],[Gender].[YTD]} ON ROWS
    >> FROM [Sales];
    >>
    >> returns (correctly):
    >> Axis #0:
    >> {}
    >> Axis #1:
    >> {[Time].[1997]}
    >> {[Time].[1997].[Q1]}
    >> {[Time].[1997].[Q2]}
    >> {[Time].[1997].[Q3]}
    >> {[Time].[1997].[Q4]}
    >> Axis #2:
    >> {[Gender].[M]}
    >> {[Gender].[YTD]}
    >> Row #0: 135,215
    >> Row #0: 33,381
    >> Row #0: 31,618
    >> Row #0: 33,249
    >> Row #0: 36,967
    >> Row #1: 135,215.0 <--- [Time].[1997] is aggregated correctly
    >> Row #1: 33,381.0
    >> Row #1: 64,999.0
    >> Row #1: 98,248.0
    >> Row #1: 135,215.0
    >>
    >> whereas:
    >>
    >> with
    >> SET [VTime] AS 'VisualTotals({[Time].[1997],[Time].[1997].Children})'
    >> member [Gender].[YTD] as 'AGGREGATE(YTD(),[Gender].[M])', format_string =
    >> "#,###00.0"
    >> select
    >> {[VTime]} ON COLUMNS,
    >> {[Gender].[M],[Gender].[YTD]} ON ROWS
    >> FROM [Sales];
    >>
    >> gives:
    >> Axis #0:
    >> {}
    >> Axis #1:
    >> {[Time].[1997]}
    >> {[Time].[1997].[Q1]}
    >> {[Time].[1997].[Q2]}
    >> {[Time].[1997].[Q3]}
    >> {[Time].[1997].[Q4]}
    >> Axis #2:
    >> {[Gender].[M]}
    >> {[Gender].[YTD]}
    >> Row #0: 135,215
    >> Row #0: 33,381
    >> Row #0: 31,618
    >> Row #0: 33,249
    >> Row #0: 36,967
    >> Row #1: 331,843.0 <<<< this should be 135,125 as far as I can tell
    >> Row #1: 33,381.0
    >> Row #1: 64,999.0
    >> Row #1: 98,248.0
    >> Row #1: 135,215.0
    >>
    >> note axis #2 and axis #2 are identical in both cases, so I'm assuming it
    >> has to do with the internal handling of VisualTotals...
    >>
    >> any ideas - or should I just create a testcase for this and open a new
    >> JIRA issue?
    >> Cheers
    >> Ati
    >>
    >> _______________________________________________
    >> Mondrian mailing list
    >> Mondrian (AT) pentaho (DOT) org
    >> http://lists.pentaho.org/mailman/listinfo/mondrian
    >>
    >>

    >
    > _______________________________________________
    > Mondrian mailing list
    > Mondrian (AT) pentaho (DOT) org
    > http://lists.pentaho.org/mailman/listinfo/mondrian
    >
    >


    _______________________________________________
    Mondrian mailing list
    Mondrian (AT) pentaho (DOT) org
    http://lists.pentaho.org/mailman/listinfo/mondrian

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.