Uploaded image for project: 'Pentaho Analysis - Mondrian'
  1. Pentaho Analysis - Mondrian
  2. MONDRIAN-939

VisualTotals returning incorrect values with aggregate members

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Closed
    • Severity: Medium
    • Resolution: Cannot Reproduce
    • Affects Version/s: None
    • Fix Version/s: None
    • Component/s: None
    • Labels:
      None
    • Notice:
      When an issue is open, the "Fix Version/s" field conveys a target, not necessarily a commitment. When an issue is closed, the "Fix Version/s" field conveys the version that the issue was fixed in.

      Description

      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...

        Attachments

          Activity

            People

            Assignee:
            Unassigned Unassigned
            Reporter:
            uramisten uramisten
            Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

              Dates

              Created:
              Updated:
              Resolved: